SQL Server Instance – generate tsql to meet tempdb best practices and display the current configuration

Goal

Generate tsql code required to meet tempdb best practices configuration and display the current configuration

My way of achieving it

Using SSMS, tsql, xp_cmdshell

The backbone for this script was a script made a while ago by VijayRod, and reviewed by AkshayM that was checking tempdb configuration by comparing the current configuration to best practices recommended by Microsoft at that time. Because sometime we need if configuration is not compliant to modify the tempdb settings I decided to use their script and add the code that will generate the tsql commands used to configure tempdb database to meet the requirements. This version of the script has some limitations and assumes the following
– when it comes to tempdb databases that have files created in multiple locations ( different drives ), by default the script will take in consideration only the biggest drive when it comes to create the files.
– it assumes that the free space left on the drive where the files will be created will be only 1 GB from the drive capacity. If you want to leave more space then replace the 1024 in the script in this line – SELECT @availablespace=(MAX(diskspacefreeinmb)-1024)/@suggestedDataFilecount FROM @dbfilesdrivemountpoint WHERE filetype = ‘DATA’ –
– it assumes that the maximum size will be the initial size + 10MB. If you want to have a maximum size bigger then replace 10 in the script in this line – SELECT @maxsize = CONVERT(nvarchar,(@availablespace+10))+’MB’ — assumes a maximum size of initial size + another 10 MB

The output of the script looks like below depending on the current tempdb database configuration

tempdbbpgencomm1

The script can be downloaded from here