Optimizing ConfigMgr databases

Installing and configuring the CMMonitor database

Background

The SCCM 2007 and Configuration Manager built-in site maintenance index rebuild task does not always run. The consequence is a ConfigMgr database with indexes that are fragmented and statistics that are out of date. The impact is a database that continues to run slower and slower.

The CMMonitor database is designed to; mitigate the out of date indexes and statistics, not make any changes to the existing ConfigMgr database and be configurable to address optimizing other databases as needed.

Installation scripts can be found here:

CMMonitor Demo (DB optimization)

On the Configuration Manager Database server, open SSMS (SQL Server Management Studio) and open the 3 scripts referenced above.

Step 1

Create the CMMonitor database – from SSMS reference script ‘5 -CMMonitorDB.sql’. You will need to change the location of the database data files to match your server directory structure. Paths highlighted below:

CREATE DATABASE [CMMonitor] ON PRIMARY

( NAME = N’CMMonitor’, FILENAME = N’I:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CMMonitor.mdf’ , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )

LOG ON

( NAME = N’CMMonitor_log’, FILENAME = N’T:\Log\CMMonitor_log.ldf’ , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 5120KB )

GO

Execute this script. Once run, you should receive the following message:

Command(s) completed successfully.

Step 2

Populate the CMMonitor database – use the following script:

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

NOTE: make sure you change the statement on the first page of this script:

USE MASTER

to

USE CMMONITOR

GO

Execute this script. Once run, you should receive the following message:

Command(s) completed successfully.

Step 3

Create the SQL Server Agent task – from SSMS reference script ‘7-Create Agent task.sql’. This step will create the task to automate the index optimize and statistics update process.

Execute this script. Once run, you should receive the following message:

Command(s) completed successfully.

Using SSMS, browse to the SQL Server Agent node, expand that node and the Jobs node below that. You should see a minimum of these jobs defined.

clip_image002

Select job named ‘IndexOptimize – AME’, right click then properties. Rename job to suit your server, site code, etc.

clip_image004

Next, click Steps, then Edit:

clip_image006

Then, edit the names of the databases you want to optimize, adding a comma between each database. Normally you will want to include the SUSDB if that database is present with the site database.

clip_image008

Click OK, then OK again to save. Note the schedule is set to run 1x/week on Monday, started at 6:00pm. Alter this schedule as appropriate.

Final note

Consider adding a schedule to the SQL Server Agent task ‘CommandLog Cleanup’. This will clean out entries from the CMMonitor database, CommandLog table older than 30 days.

Advertisements
This entry was posted in ConfigMgr, SQL Server. Bookmark the permalink.

32 Responses to Optimizing ConfigMgr databases

  1. Patrik Frisk says:

    Hi Steve

    I´m going to test your steps above, but when looking for the CMmonitorDB.sql that you use too build the CMmonitor-database I can´t find the file when following the link “CMMonitor Demo ”

    Could you please help me to get the sql script

    /Patrik

  2. Patrik Frisk says:

    Thanks a lot for the doc 🙂

    /Patrik

  3. Pingback: Guide on how to Configure SQL Server Index Optimization with Ola Hallengrens Maintenance Solution | CMTrace - System Center

  4. Pingback: ConfigMgr 2012 Index Optimization | Steve Thompson [MVP]

  5. Pingback: ConfigMgr 2012 RTM to R2 Upgrade checklist | Steve Thompson [MVP]

  6. Pingback: Dev IT Connections Session Files | Steve Thompson [MVP]

  7. Pingback: Rebuilding Indexes | Dave Black [System Centre Administrator]

  8. JB says:

    Steve, just an FYI – I was just setting this up for the first time on a new build, and looks like the download was updated by Ola today (Nov. 24, 2014). After execution, the “IndexOptimize – AME” job, is no longer created, only the “IndexOptimize – USER_DATABASES” job. Which I am using/configuring based on the TechEd NA, 2014 session – PCIT-B320 – “System Center Configuration Manager Community Jewels”. They also reference your post.

    • configmgrmvp says:

      Using the IndexOptimize – User_databases option will work fine. The custom SQL Server agent task I posted offers a little more control over the process.

  9. JB says:

    Got it, thx, my bad, did not read carefully enough!

  10. Pingback: SCU 2015 – Top 10 ConfigMgr 2012 Issues – SQL Content Links | Steve Thompson [MVP]

  11. Pingback: SQL maintenance with Ola’s script | The Empty Garden

  12. Lewis says:

    Can you tell me what the “Update Statistics” option does in SCCM? I get it when I right click an application package. This isn’t the same as SQL update statistics, is it?

    • Steve T says:

      No. Those statistics deal with the Application statistics — how many devices, users, etc. They appear in the information window with each app.

  13. Jesse says:

    I’m getting an error messages saying the following:

    Msg 1807, Level 16, State 3, Line 3
    Could not obtain exclusive lock on database ‘model’. Retry the operation later.
    Msg 1802, Level 16, State 4, Line 3
    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
    Msg 5011, Level 14, State 5, Line 2
    User does not have permission to alter database ‘CMMonitor’, the database does not exist, or the database is not in a state that allows access checks.
    Msg 5069, Level 16, State 1, Line 2
    ALTER DATABASE statement failed.

    I’m assuming the first error is causing all subsequent errors. Any ideas how to get around this? Thanks

    • Steve T says:

      I’ve never seen that, sounds like you are attempting to use model which would be wrong. Follow the document, you should be OK.

  14. Pingback: Talking Database II - The Config Ninja Blog - Site Home - TechNet Blogs

  15. Denver says:

    Is it okay if we change the compatibility level of the CMMonitor db to 110?

  16. mishaua says:

    Is optimization of the configmgr database still relevant in sccm current branch? Also what is the benefit of creating the cmmonitor db and using ola’s script on it instead of just running it against the main configmgr database?

    • Treker says:

      Yes it is. If you don’t create CMMonitor database, Ola’s script modifies the master database (by default). I’m not fond of that.

  17. Andy says:

    Hi thanks for the scripts very useful, I have a problem though when creating ‘7-Create Agent task.sql’ it completes ok but i only get the job IndexOptimize – MMS2013 created

    • Treker says:

      You can rename your SQL Server Agent job task. That part was not explicitly called out in the instructions.

      • Andy says:

        hi thanks. Sure its not the script name that’s the problem. none of the other SQL tasks get created only IndexOptimize task.

  18. Pingback: Installing and configuring SQL Server for Configuration Manager | Steve Thompson [MVP]

  19. Pingback: Configmgr SQL Server Sizing and Optimization – configmgr

  20. Pingback: Why you should not use the ConfigMgr rebuild index task | Steve Thompson [MVP]

  21. Pingback: Optimizing SCCM Databases Revisited | Steve Thompson [MVP]

  22. Pingback: Top 5 blog posts for 2016 | Steve Thompson [MVP]

  23. Pingback: SCCM / Windows Server

  24. Pingback: SUP/WSUS Maintenance and recommendations – DeployWindows•Info

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s