Properly configure SQL Server ConfigMgr database

Ideally, this will take place before you install ConfigMgr!

First, estimate size(s) of the databases:

How? Use the  SCCM DB Sizing estimator!

Shown is an example of the Database sizing worksheet, based on estimated client counts:


Anthony has documented this approach as follows:

Get the sizing estimator here.

Using the estimator to calculate the number and size of the data files, pre-create the SQL Server database for ConfigMgr. Key take away, will be the total estimate sizes.

Multiple data files may be appropriate for a couple of reasons. SQL Server is multi-threaded, which means you may get performance gains by having multiple data files. Further, these data files could be located on different logical drives to further distribute load, and increase IOPS. on As a general rule of thumb, use the following as a guideline:

Total Estimate Client Count

Number of CM data files

< 10K


10K to 25K


>25K to 50K




Other factors to consider:

  • Drives dedicated for SQL data and logs should be formatted with NTFS in 64K block size.
  • Always place TempDB data and logs on a dedicated logical drive
  • DB files should be equal size (hint: divide total estimated size / # data files)
  • Create data files to be an even GB
  • Always pre-create the db files prior to installing CM – auto-growth is bad.
  • If you enable auto-growth:
    • Do not use percent (%) growth
    • Set auto-growth to be evenly divisible into a whole GB; (i.e. 512 or 1024MB)
  • Monitor available free space
  • Always use a single LOG file
  • If possible, place LOG file on separate logical drive

It is possible to migrate a single data file to multiple data files. Use this reference to get you started. It is an older article, the screen shots are missing, however the technique is valid.

Note: Be cautious if you attempt to implement in a multi-site hierarchy! Table partitioning may present an issue.

Final thoughts: As always, there is never a right or wrong way to do this, this is intended be used a guideline only. Test in a lab environment first. No cute furry animals were harmed with this process and never stick your fingers inside the cage.

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

18 Responses to Properly configure SQL Server ConfigMgr database

  1. This is the first time I’ve ever seen a recommendation for data files based on client count. All previous references I’ve seen are based on SQL Server CPU count. What changed?

    • ConfigMgrMVP says:

      I’ve seen various subjective references to NUMA settings and affect on IO. You did see my Final thoughts section “As always, there is never a right or wrong way to do this, this is intended be used a guideline only” comment? If you have references that show the proper number of data files for CM, based on CPU – please post them?

  2. John Nelson Number2 says:

    Correction: I think there IS a wrong way to do it…one DB file on a 3-disk raid 5 volume 🙂

  3. John says:

    Thank you for a good post.
    Quick question: should the TempDB drive also be formated in 64K?

  4. Al Howard says:

    I’ve just used the SCCM DB Sizing estimator for 6000 clients on a remote SQL 2014 server. My major concern is the autogrowth size of the ConfigMgr Log File (estimated at 6000 MB for a log file estimated at 7500 Mb). Is this a realistic amount as this could potentially grind my dB server to a halt.

    • ConfigMgrMVP says:

      Not sure how you got those numbers. Using the linked calculator with 6,000 clients. CM DB would be 30,000mb (~30gb), log file 7,500mb which is 25% of the CM DB. Very reasonable starting point. Use SQL 2016…

  5. Al Howard says:

    I realised for the log file autogrowth was set to 80% of the size of the log file. Can’t say if this is an accident but i removed the formular and currently set my autogrowth to 1025 MB.

  6. SRT says:

    Hi Steve,

    With regards to WSUS. The spreadsheet recommends splitting the DB in two (15GB each). I have no problem doing this, but when I run the WsusUtil.exe POSTINSTALL. I get the following errors in the Log file:

    System.Data.SqlClient.SqlException (0x80131904): Invalid object name ‘SUSDB.dbo.tbSchemaVersion’.
    Error Number:208,State:1,Class:16

    If I don’t manually pre-create the SUSDB everything works successfully. I’m obviously missing a step, can you point me in the right direction please.

    • ConfigMgrMVP says:

      This is a recommendation only… you’d probably be fine with a single data file of 15gb. Don’t recall needed to run WSUSUtil when used with SCCM…

  7. Shawn B says:

    Hi Steve, the post you refer to regarding migrating from a single file to multiple files no longer exists. Do you have copy of that or any other references?

    • ConfigMgrMVP says:

      Hi Shawn, Two years ago, I archived that article, which was 5 years old at that time! The screen shots were long gone.
      I have stayed in touch with the original author… if John approves, should I just paste it as is, or would you need screenshots, etc (which will take more time).

  8. Nico says:

    Hi Steve
    I just got a question about the SCCM db files. Is there a reason to split the SCCM db files to the number of CPU cores count or does this only apply to the temp db?
    You stated the number of CM db files based on client count, why is that so? Is there a technical explanation?

    • ConfigMgrMVP says:

      For the SCCM DB, the sizing calculator on this page is a good starting point for using Total client count in estimating number of DB files. There are some advantages for backup performance with LARGE dbs and more files.

      For a SQL instance running SCCM, TempDB seems to benefit the most with (8) TempDB files.

      With SQL Server db files, it’s all about the IOPS… see this article for more information:

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.