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


Set-SqlTempDbConfiguration

This command will help you set tempdb data and log files according to best practices.

Function to calculate tempdb size and file configurations based on passed parameters, calculated values, and Microsoftbest practices. User must declare SQL Server to be configured and total data file size as mandatory values. Function will then calculate 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). The function will not perform any actions that would shrink or delete data files. If a user desires this, they will need to reduce tempdb so that it is “smaller” than what the function will size it to before running the function.

Examples

To create tempdb with a number of datafiles equal to the logical cores where each one is equal to 1024MB divided by number of logical cores and a log file of 250MB

Set-SqlTempDbConfiguration -SqlServer sql2014 -DataFileSizeMB 1024

To create tempdb with a number of datafiles equal to the logical cores where each one is equal to 125MB and a log file of 250MB

Set-SqlTempDbConfiguration -SqlServer sql2014 -DataFileSizeMB 1000 -DataFileCount 8

To provide a SQL script output to configure tempdb according to the passed parameters

Set-SqlTempDbConfiguration -SqlServer sql2014 -DataFileSizeMB 1024 -Script

To return a PSObject representing tempdb configuration.

Set-SqlTempDbConfiguration -SqlServer sql2014 -DataFileSizeMB 1024 -Script

References

Recommendations to reduce allocation contention in SQL Server tempdb database – microsoft.com
Compilation of SQL Server TempDB IO Best Practices – microsoft.com
A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core – Paul S. Randal

Author

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

From PowerShell, execute   Get-Help Set-SqlTempDbConfiguration -Detailed   for more information on this function. Want to see the source code? See it on GitHub