dbatools is a free PowerShell module with over 180 SQL Server administration, best practice and migration commands included.


Test-SqlTempDbConfiguration

Evaluates tempdb aganst 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, as 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.

Other rules can be added at a future date. If any of these rules don’t match recommended values, a warning will be thrown.

Examples

To check a SQL Server instance:

Test-SqlTempDBConfiguration -SqlServer localhost

Screenshots

2016-07-19_9-39-52

 

 

 

 

References

Based off of Amit Bannerjee’s (@banerjeeamit) Get-TempDB function.

Author

This command was created by Mike Fal. You can find Mike on Twitter, mikefal.net and LinkedIn.

Get-Help

From PowerShell, execute Get-Help Test-SqlTempDbConfiguration -Detailed for more information on this function.

Source Code

Want to see the source code? View Test-SqlTempDbConfiguration.ps1 on GitHub
 

Related commands