12 Days of dbatools – Sixth Day3 min read

---

On the first day of Christmas dbatools gave to me: Find-DbaCommand

On the second day of Christmas dbatools gave to me: Get-DbaCmsRegServer

On the third day of Christmas dbatools gave to me: DbaMaxMemory

On the fourth day of Christmas dbatools gave to me: DbaAgentJob

On the fifth day of Christmas dbatools gave to me: DbaLogin

On the sixth day of Christmas dbatools gave to me: DbaTempDbConfig

---

Command Information:

Half way through, and I hope you are enjoying the series and holiday season.  On the sixth day we will talk about a confusing topic for most in the DBA world.  TempDB is a mystery to most and a good friend to few.  Well this is another example of how dbatools takes the guess work out of TempDB configurations.  The Test-DbaTempDbConfig command will look at your SQL Instance and calculate your TempDB configurations based on best practices in the industry.  That means you do not have to learn that hard math you took one time way back in college.  Lets get started to see what you can do with this command.

Command Description: 

Set-DbaTempDbConfig

Calculates tempdb size and file configurations based on passed parameters, calculated values, and Microsoft best practices. User must declare SQL Server to be configured and total data file size as mandatory values. Function then calculates the number of data files based on logical cores on the target host and create evenly sized data files based on the total data size declared by the user, with a log file 25% of the total data file size.

Other parameters can adjust the settings as the user desires (such as different file paths, number of data files, and log file size). No functions that shrink or delete data files are performed. If you wish to do this, you will need to resize tempdb so that it is “smaller” than what the function will size it to before running the function.

Test-DbaTempDbConfig

Evaluates tempdb against a set of rules to match best practices. The rules are:

* TF 1118 enabled – Is Trace Flag 1118 enabled (See KB328551).
* File Count – Does the count of data files in tempdb match the number of logical cores, up to 8?
* File Growth – Are any files set to have percentage growth? Best practice is all files have an explicit growth value.
* File Location – Is tempdb located on the C:\? Best practice says to locate it elsewhere.
* File MaxSize Set (optional) – Do any files have a max size value? Max size could cause tempdb problems if it isn’t allowed to grow.
* Data File Size Equal – Are the sizes of all the tempdb data files the same?

Other rules can be added at a future date.

Command Example:

Wow, look at all of this great information returned about my SQL Instance.  So lets run through these results.

• TF 1118 Enabled – This rule checks to see if Trace Flag 1118 is enabled.  This is a must have for instances below SQL 2016.
• File Count – This rule takes in to account your processor count and based on best practices tells you how many TempDB data files you should have.
• File Growth in Percent – This rule checks the current file(s) to see if auto-growth is in percentage which is bad!!
• File Location – This rule checks to make sure TempDB data and log files are not on the C:\ drive.
• File MaxSize Set – This rule makes sure that you don’t have a max size set for your TempDB data file to grow.

Advanced Command Usage:

Now once you get your information from the Test command, you can then Set the settings based on what you need.