SCRIPT: How to configure TempDB files properly?

 USE [master]
GO
SET NOCOUNT ON;
 
-- Set these values
DECLARE
 @Debug BIT = 1 --1=Information Only; 0=Execute Command
,@DataFileSize VARCHAR(10) = '512' --Total size of all Tempdb files; to be divided per file (in mb)
,@DataGrowth VARCHAR(10) = '1024' --Data Autogrowth size (in mb)
,@LogFileSize VARCHAR(10) = '2554' --Size of Log (in mb)
,@LogGrowth VARCHAR(10) = '256' --Log Autogrowth size (in mb)
,@DataName VARCHAR(25) = 'Tempdb_Data' --Desired file name for data files
,@DataLocation VARCHAR(250) = 'D:\TempDB' --Data file location
,@LogName VARCHAR(25) = 'Tempdb_Log' --Desired file name for log file
,@LogLocation VARCHAR(250) = 'D:\TempDB' --Log file location
,@DataFilesNumber INT = 4 -- Number of Data files
 
-- Do not change
,@Count INT = 2
,@OldData VARCHAR(25)
,@OldLog VARCHAR(25)
 
SELECT @OldData = Name FROM sys.master_files WHERE database_id = 2 AND file_id = 1
SELECT @OldLog = Name FROM sys.master_files WHERE database_id = 2 AND file_id = 2
 
-- Modify original single log and data files to follow desired inputs
DECLARE @SQL VARCHAR(MAX) = '
ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'''+@OldLog+''', NEWNAME= N'''+@LogName+''', FILENAME= N'''+@LogLocation+'\Tempdb_log.ldf'', SIZE= '+@LogFileSize+'MB, FILEGROWTH= '+@LogGrowth+'MB);
 
ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'''+@OldData+''', NEWNAME= N'''+@DataName+'1'', FILENAME= N'''+@DataLocation+'\Tempdb1.mdf'', SIZE= '+@DataFileSize+'MB, FILEGROWTH= '+@DataGrowth+'MB);
'
 
-- Generates additional data files up to calculated limit
WHILE(@Count<=@DataFilesNumber)
BEGIN
SET @SQL+= '
ALTER DATABASE [tempdb]
ADD FILE (NAME= N'''+@DataName+''+CONVERT(VARCHAR,@Count)+''', FILENAME= N'''+@DataLocation+'\Tempdb'+CONVERT(VARCHAR,@Count)+'.ndf'', SIZE= '+@DataFileSize+'MB, FILEGROWTH= '+@DataGrowth+'MB);
'
SET @Count+=1
END
 
-- Debug=1 Prints SQL commands to execute for testing; Debug=0 will process the commands
IF(@Debug=1)
PRINT @SQL
IF(@Debug=0)
EXEC (@SQL)

Comments