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


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 Microsoft best 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.


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


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


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


To return a PSObject representing tempdb configuration.



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


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


From PowerShell, execute Get-Help Set-DbaTempDbConfiguration -Detailed for more information on this function.

Source Code

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

Related commands