TempDB in SCOM (script)

  • Article

Following recommendation to separate TempDB on multiple files (based on CPU numbers), my colleague Thomas Andersen (SQL PFE) provided me a SQL script for that.

Thanks Mr A!

Script to separate TempDB on multiple files, according to CPU numbers.

-- Add files to tempdb to make the number of datafiles equal to the number of CPUs

USE tempdb
SET nocount ON 
DECLARE @t TABLE(
                   i INT,
                   n sysname,
                   ivalue INT,
                   cvalue sysname
                   )
DECLARE @ProcessorCount INT
DECLARE @TempFileCount INT
DECLARE @TempFileName sysname
DECLARE @TempFileNameNew sysname
DECLARE @cmd NVARCHAR(1000)
DECLARE @i INT
DECLARE @size BIGINT
INSERT INTO @t
                                     EXEC xp_msver
                                                  N'ProcessorCount'
SELECT @ProcessorCount = ivalue
FROM @t -- Get processor Count
if @ProcessorCount > 8 Set @ProcessorCount = 8

 

SELECT @TempFileCount = count(* )
FROM tempdb..sysfiles -- current number of files
WHERE groupid <> 0
SELECT @TempFileName = filename
FROM tempdb..sysfiles -- name of file
WHERE fileid = 1
SELECT @size = (size
                  / 128)
                 / @ProcessorCount
FROM tempdb..sysfiles -- compute new size and growth
WHERE fileid = 1
IF @size < 512
  BEGIN
    SET @size = 512
    SET @cmd = 'ALTER DATABASE tempdb MODIFY FILE (NAME=''tempdev'''
                 + ',SIZE='
                 + rtrim(convert(CHAR(10),@size))
                 + 'MB'
                 + ',FILEGROWTH='
                 + rtrim(convert(CHAR(10),@size))
                 + 'MB'
                 + ')'
-- SELECT @cmd -- Add the file
    EXEC sp_executesql
      @cmd
  END
IF @size < 1
  SET @size = 1
SET @i = @TempFileCount
           + 1 -- First new file is number 2
WHILE @i <= @ProcessorCount
  BEGIN
    SET @TempFileNameNew = replace(@TempFileName,'tempdb.mdf','tempdev'
                                                                + rtrim(convert(CHAR(2),@i))
                                                                + '.ndf')
    SET @cmd = 'ALTER DATABASE tempdb add FILE (NAME=''tempdev'
                 + rtrim(convert(CHAR(2),@i))
                 + ''',FILENAME='''
                 + @TempFileNameNew
                 + ''''
                 + ',SIZE='
                 + rtrim(convert(CHAR(10),@size))
                 + 'MB'
                 + ',FILEGROWTH='
                 + rtrim(convert(CHAR(10),@size))
                 + 'MB'
                 + ')'
-- SELECT @cmd -- Add the file
    EXEC sp_executesql
      @cmd
    SET @i = @i
               + 1
  END
SET @cmd = 'ALTER DATABASE tempdb MODIFY FILE (NAME=''templog'''
             --+ ''',FILENAME=''' + @TempFileNameNew + ''''
           + ',SIZE='
             + rtrim(convert(CHAR(10),@size))
             + 'MB'
             + ',FILEGROWTH='
             + rtrim(convert(CHAR(10),@size))
             + 'MB'
             + ')'
--SELECT @cmd -- Add the file
EXEC sp_executesql
  @cmd
PRINT convert(VARCHAR(19),getdate(),121) + ' - Created or modified ' + CONVERT(VARCHAR(3),@TempFileCount) + ' tempdb data file(s)'
USE tempdb