Script to Create Multiple, Same-Sized Tempdb Files

This script will take a tempdb database with one database file, and create a set of equally-sized files. You can set how much total space you want the files to use, and how many files to have (in total).

From BOL, as a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs. Make each data file the same size; this allows for optimal proportional-fill performance.

"A more pragmatic approach however, is to have a 1:1 mapping between files and logical CPUs up to eight, and then add files if you continue to see allocation contention or if you're looking to push the I/O subsystem harder. The performance benefit from adding files diminishes each time, and in our experience, eight is the sweet spot, especially if you're implementing this as a pro-active measure." (Configuration Best Practices for SQL Server Tempdb--Multiple Files)

These calculations assume that currently the tempdb database has only one datafile. Also, the log file for tempdb is assumed to be on a different drive.

USE master
SET NOCOUNT ON

-- Enter your values here.  
RAISERROR('Comment out this line once you have entered your values here!', 20, 1) WITH NOWAIT, LOG

DECLARE @TempdbTotalSizeGB INT               SET @TempdbTotalSizeGB = 266
DECLARE @NumberOfDataFiles INT               SET @NumberOfDataFiles =   8
DECLARE @TempdbFolder      NVARCHAR(260)     SET @TempdbFolder      = 'E:\MSSQL_TEMPDB\'

-- Check that there is only one tempdb datafile.  
DECLARE @DataFileNum INT

SELECT @DataFileNum = COUNT(*)
  FROM msdb.sys.master_files
 WHERE database_id = DB_ID('tempdb')
   AND type_desc = 'ROWS'

IF @DataFileNum <> 1
    RAISERROR('Tempdb does not have exactly one file - cannot use this script', 20, 1) WITH NOWAIT, LOG

-- Check that the path exists. 
DECLARE @t TABLE (FileExists BIT, FileIsDirectory BIT, ParentDirectoryExists BIT)
INSERT @t EXEC master..xp_fileexist @TempdbFolder

IF NOT EXISTS (SELECT * FROM @t WHERE FileIsDirectory = 1)
    RAISERROR('Folder "%s" does not exist.', 20, 1, @TempdbFolder) WITH NOWAIT, LOG

-- Calculate the size of each tempdb datafile. We're leaving 10% free for extra space. YMMV. 
DECLARE @DataFileSizeGB INT
SET @DataFileSizeGB = ((9 * @TempdbTotalSizeGB) / 10) / @NumberOfDatafiles

-- First, modify the existing tempdb datafile's size. 
DECLARE @CrLf NVARCHAR(2)
SET @CrLf = CHAR(13) + CHAR(10)

DECLARE @Sql NVARCHAR(4000)

SET @Sql = 'ALTER DATABASE tempdb '                                                                       + @CrLf
         + '   MODIFY FILE (NAME = ''tempdev'', '                                                         + @CrLf
         + '                SIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB, '                     + @CrLf
         + '                FILEGROWTH = 0KB, MAXSIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB)' + @CrLf

RAISERROR(@Sql, 10, 1) WITH NOWAIT
EXEC sp_executesql @Sql

-- Now create the additional datafiles for tempdb. 
SET @DataFileNum = 1

WHILE @DataFileNum < @NumberOfDatafiles
BEGIN
    SET @Sql = 'ALTER DATABASE tempdb '                                                                                 + @CrLf
             + '  ADD FILE (NAME = ''tempdev' + CAST(@DataFileNum AS NVARCHAR(50)) + ''', '                             + @CrLf
             + '            FILENAME = ''' + @TempdbFolder + 'tempdb' + CAST(@DataFileNum AS NVARCHAR(50)) + '.mdf'', ' + @CrLf
             + '            SIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB, '                                   + @CrLf
             + '            FILEGROWTH = 0KB, MAXSIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB) '              + @CrLf

    RAISERROR(@Sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @Sql
    SET @DataFileNum = @DataFileNum + 1
END

-- Finally, set the logfile's autogrowth for the tempdb database. The Microsoft  
-- default of 1% is not best practice. 
DECLARE @LogFileSizeGB INT
SET @LogFileSizeGB = 1

SET @Sql = 'ALTER DATABASE tempdb '                                                                            + @CrLf
         + '   MODIFY FILE (NAME = ''templog'', FILEGROWTH = ' + CAST(@LogFileSizeGB AS NVARCHAR(50)) + 'GB) ' + @CrLf

RAISERROR(@Sql, 10, 1) WITH NOWAIT
EXEC sp_executesql @Sql

GO