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

10,000

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

clip_image002

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!

image

The SQL Server 2016 TempDB database files.

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

3 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]

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