Script to validate Tempdb configuration in SQL Server

Introduction

This T-SQL sample script illustrates how to check tempdb configuration per best practices. Tempdb is a system database is highly used by Sql Server and an optimal tempdb configuration is generally important for smooth running of the Sql instance.

Scenarios

Ideal tempdb configuration is a frequently asked question by IT administrators in the TechNet forums. IT professionals want to check tempdb best practices by script, for example,

Checking current number of tempdb data files
Suggesting ideal number of tempdb data files (subject to testing).
Checking if tempdb files are created of equal size.
Checking if tempdb files are created of equal growth (increment and type).
Checking if tempdb trace flag (for uniform extent allocation) is enabled and required depending on Sql version
Suggesting if tempdb configuration is healthy if no suggestions were made
Ability to run script in Sql 2005/2008/2008R2/2012.

Script

Please open a new query window in Sql Server Management Studio. Please paste the entire script in the query window and then please Execute the script.
A sample output is indicated below for reference.

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

SQL

Edit|Remove

  -- checks if tempdb files are created of equal size
 
    IF (EXISTS(  SELECT name,
                                   size,
                                   physical_name
               FROM  tempdb.sys.database_files
               WHERE  type_desc = 'ROWS'
               AND size <> (SELECT MAX(size)                  
               FROM tempdb.sys.database_files 
               WHERE type_desc = 'ROWS')))
       BEGIN 
              SET @bSuggestions = 1;
              PRINT N'File sizes of tempdb data files do not appear to be equal. '
                             + N'Please verify initial size is same for all tempdb data files.';
       END

  -- checks if tempdb files are created of equal size 
  
    IF (EXISTS(  SELECT name, 
                                   size, 
                                   physical_name 
               FROM  tempdb.sys.database_files 
               WHERE  type_desc = 'ROWS' 
               AND size <> (SELECT MAX(size)                   
               FROM tempdb.sys.database_files  
               WHERE type_desc = 'ROWS'))) 
       BEGIN  
              SET @bSuggestions = 1; 
              PRINT N'File sizes of tempdb data files do not appear to be equal. ' 
                             + N'Please verify initial size is same for all tempdb data files.'; 
       END

Additional Resources

Related forum threads: