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

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Test-DbaTempDbConfiguration

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:

 

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-DbaTempDbConfiguration -Detailed for more information on this function.

Source Code

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

Related commands