Proper TempDB creation for Configuration Manager

As a consultant, one of the major issues I see with SQL Server configuration for Configuration Manager is allowing default settings for TempDB.

The default TempDB size for all versions of SQL Server (prior to SQL 2016 – more on this later), is set to 8MB, file growth 1 MB.

Recently checked a Configuration Manager site SQL Server database properties, and TempDB had grown to 1.5 GB. This was with the default settings of 8MB size, 1 MB file growth. Can you imagine how many data file fragments occurred to get to 1.5GB?!

When SQL Server (MSSQLServer) service is restarted, TempDB gets reset to? The default size! And, the fun starts all over again.

First steps in correcting this; estimate the size of TempDB. There are some great calculators out there, Kent Agerlund did a nice job taking standard MSFT recommendations and converting it to an Excel “calculator”. This is a great starting point to determine the total size of TempDB, based on estimated client counts. Typically TempDB will be approximately 25-30% of the total, estimated CM DB size.

The only flaw with this calculator, it calculates a single TempDB file. TempDB can greatly benefit from multiple data files.

Let’s work through an example:

Projected # clients


Estimated CM data size

54 GB

Estimated TempDB size

16 GB

Making the assumption that your server has at least 2 processors and 8 cores, you’d want to start TempDB at least four (4) equal sized files, of 4GB each. Now, there may be some benefit in creating TempDB with eight (8) total, equal sized data files. Under no circumstance should you create more than 8 data files.

Now, to create, you can use the SQL Server management studio and add the files… even easier, use PowerShell.

To create using PowerShell follow the following instructions:

Open an elevated PowerShell prompt, create the additional SQL TempDB database files and set their initial size to a total of 16 GB by running the following command (the command is wrapped and should be one line):

Invoke-Sqlcmd –QueryTimeout 0
-InputFile C:\Setup\Scripts\ConfigureTempDB.sql


The SQL TempDB database files.

Note: change paths as appropriate – sample script can be downloaded for free, from book sample files, from my Reporting Services book.

As a side note, SQL Server 2016 now creates the following default TempDB settings. Probably still too small for most production instances. None the less, much better!


The SQL Server 2016 TempDB database files.

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

6 Responses to Proper TempDB creation for Configuration Manager

  1. Pingback: Proper TempDB creation for Configuration Manager | Skatterbrainz Blog

  2. Pingback: Configmgr Sizing Worksheets – configmgr

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

  4. Pingback: Links from our MMS ConfigMgr precon session – CTGlobal

  5. adampskib says:

    Hi Steve! We met at MMSJAZZ last year and it was great to meet you. You were very helpful, thanks!
    I have one tempdb right now and it is approx 1300MB (not the size on disk right now, just the capacity of the database properties when I right click on it in SSMS). To improve my situation I need to create 4 x 3225MB tempdb. Should I stop all ConfigMgr services and (via SSMS) delete existing tempdb files and recreate new ones with desired size and autogrowth settings?

    • ConfigMgrMVP says:

      I’d recommend creating 8 tempdb files of equal sizes. If you have a dedicated drive for TempDB, fill the drive… TempDB database and files are created each time you stop and start MSSQLServer service. Thanks for coming to MSJAZZ – pleasure meeting you as well.

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 )

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.