TempDB in SCOM (script)


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

 

 

 

Comments (0)

Skip to main content