locked
Enable Re-Indexing Maintenance Task for SCCM 2012 R2 Database? RRS feed

  • Question

  • If I use the MaintenanceSolution.sql script provided by Ola Hallengren here: 

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    and then use this SQL script weekly to rebuild the SCCM 2012 R2 indexes:

    EXECUTE dbo.IndexOptimize
     @Databases = 'USER_DATABASES',
     @FragmentationLow = NULL,
     @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
     @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
     @FragmentationLevel1 = 5,
     @FragmentationLevel2 = 30,
     @UpdateStatistics = 'ALL',
     @OnlyModifiedStatistics = 'Y'

    Then do I still need to enable the built-in database re-indexing maintenance task in the SCCM 2012 R2 console?

    Thanks 



    Friday, May 23, 2014 4:53 AM

Answers

All replies

  • More info on the SQL indexing can be found on this awesome recording of TechEd NA 2014:

    http://channel9.msdn.com/Events/TechEd/NorthAmerica/2014/PCIT-B320#fbid=

    To my knowledge, you do not need to enable the built-in database indexing maintenance task, if you have Ola's script running in your environment.

    Friday, May 23, 2014 5:47 AM
  • Then do I still need to enable the built-in database re-indexing maintenance task in the SCCM 2012 R2 console?


    No you don't have to use it. Ola's is much more efficient.

    Torsten Meringer | http://www.mssccmfaq.de

    Friday, May 23, 2014 6:23 AM
  • that is one way of doing it, I personally prefer not to use the site maintenance task as it does not always tend to work...

    but use a weekly SQL maintenance plan with separate schedules for each task, select the tasks 'check db integrity', 'reorganize index', 'rebuild index', 'update statistics' apply the tasks to the CM_ database (and if you wish the susdb/reportserver db)

    advantage of using the sql maintenance plan is also that you will get a proper log of the process

    Friday, May 23, 2014 6:27 AM
  • advantage of using the sql maintenance plan is also that you will get a proper log of the process


    That does Ola's solution too. It can either log to files or a table and another job can also clean up (purge) those entries.

    Torsten Meringer | http://www.mssccmfaq.de

    Friday, May 23, 2014 6:58 AM
  • Here's a post from Steve describing the steps he took in that session and also includes the SQL scripts he ran: http://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/

    Jason | http://blog.configmgrftw.com

    • Marked as answer by Ron_Ratzlaff Friday, May 23, 2014 3:50 PM
    Friday, May 23, 2014 12:36 PM
  • Thanks bro, this must be the one from Houston. I will take a look. Haven't had time to do that yet. 
    Friday, May 23, 2014 3:48 PM
  • Thanks "T", appreciate it man
    Friday, May 23, 2014 3:50 PM
  • Thanks "J"
    Friday, May 23, 2014 3:51 PM
  • In the Channel9 video, the statement is made that the  built in Rebuild Indexes "maintenance task does not always work and that it is kind of broken, but it works sometimes and not always." Can anyone provide further details regarding the state of the Rebuild Indexes maintenance task? Why does it only sometimes work? Should it be used at all? Has it been fixed with a CU since the video came out? Are there plans to fix it? 

    I need to defrag my database, and  I am trying to determine the best approach.

    Thanks!

    -Tony

    Friday, March 27, 2015 7:43 PM
  • Use this: https://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/

    Jason | http://blog.configmgrftw.com | @jasonsandys

    • Proposed as answer by Tony Chirillo Wednesday, April 1, 2015 3:17 PM
    Friday, March 27, 2015 8:05 PM
  • Is this method still considered the best for SCCM 17xx on SQL 2016?  Does the script take the SCCM DB offline while it is running?
    Tuesday, January 23, 2018 6:34 PM
  • Yes.

    No, nothing is taken offline.


    Jason | https://home.configmgrftw.com | @jasonsandys

    Tuesday, January 23, 2018 6:53 PM