SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_AddTempDBFiles]
( @Size Int,
@Growth Int,
@Drive Char(1) )
AS
/*==============================================================================
Purpose: To add 1 TempDB data file (NDF) per proc/core on the server for
better performance.
Inputs: @Size is used for the initial size the NDF should be created
on startup of the SQL Server service and/or server
@Growth is the size the NDF is permitted to extend when it needs more
room
@Drive is the drive letter where the TempDB_Data and TempDB_Log folders
are located or needed to be created on.
Example: EXEC usp_AddTempDBFiles 1000, 200, 'X'
This will created the NDFs at 1GB in size, able to create 200MB extents when
the file(s) need to grow and are located on the X: in the TempDB_Data and
TempDB_Log folders respectively.
NOTES:
*/
--
SET NOCOUNT ON
DECLARE @RegOutput VarChar(20),
@NbrProcs Int,
@SQL_Command nVarChar(4000),
@TempDBDataFileCount TinyInt,
@TempDBDataFileSize Int,
@TempDBFirstLogicalName SysName,
@TempDBDataFileId TinyInt,
@StartID TinyInt,
@EndID TinyInt,
@TempDBMissingDataFileCount TinyInt,
@TempDBLogicalName SysName,
@TempDBFirstPhysicalPath nVarChar(260)
--
-- Create secondary TempDB Files based on the number of procs
-- in the server
--
EXEC Master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
@value_name = 'NUMBER_OF_PROCESSORS',
@value = @RegOutput OUTPUT
SET @NbrProcs = @RegOutput
SELECT @StartID = 2,
@EndID = 2,
@TempDBDataFileCount = COUNT(*),
@TempDBDataFileId = MIN(File_ID)
FROM TempDB.Sys.Database_Files
WHERE Type = 0
SET @TempDBMissingDataFileCount = @NbrProcs - @TempDBDataFileCount
IF @TempDBDataFileCount <> @NbrProcs
BEGIN
SELECT @TempDBFirstLogicalName = [Name],
@TempDBFirstPhysicalPath =
SUBSTRING(physical_name,
1,
LEN(Physical_Name) - CHARINDEX('\', REVERSE(Physical_Name )) + 1)
FROM TempDB.Sys.Database_Files
WHERE File_ID = @TempDBDataFileId
SET @TempDBDataFileId = 0
-- file exists, expand it, if necessary. to make sure they will all be the same
WHILE @TempDBDataFileCount <> 0
BEGIN
SELECT @TempDBDataFileId = MIN(File_ID)
FROM TempDB.Sys.Database_Files
WHERE Type = 0
AND File_ID > @TempDBDataFileId
SELECT @TempDBLogicalName = [Name],
@TempDBDataFileSize = [Size]
FROM TempDB.Sys.Database_Files
WHERE File_ID = @TempDBDataFileId
IF @size*1024/8 > @TempDBDataFileSize
BEGIN
SET @SQL_Command = 'ALTER DATABASE TempDB MODIFY FILE (NAME = N''' +
@TempDBLogicalName +
''', SIZE = '
SET @SQL_Command = @SQL_Command +
CONVERT(VarChar(10), @size) +
'MB, FILEGROWTH = ' +
CONVERT(VarChar(10), @growth) +
'MB)'
--PRINT @SQL_Command
EXEC (@SQL_Command)
END
SET @TempDBDataFileCount = @TempDBDataFileCount - 1
END
-- Adding new data files (NDFs)
WHILE @TempDBMissingDataFileCount <> 0
BEGIN
WHILE 1 = 1
BEGIN
IF EXISTS (
SELECT *
FROM TempDB.sys.database_files
WHERE [Name] = @TempDBFirstLogicalName + CONVERT(VarChar(3), @StartID) )
SET @StartID = @StartID + 1
ELSE
BREAK
END
WHILE 1 = 1
BEGIN
IF EXISTS (
SELECT *
FROM TempDB.Sys.Database_Files
WHERE physical_name = @TempDBFirstPhysicalPath + @TempDBFirstLogicalName + CONVERT(VarChar(3), @EndID) + '.ndf' )
SET @EndID = @EndID + 1
ELSE
BREAK
END
SET @SQL_Command = 'ALTER DATABASE TempDB ADD FILE (NAME = N''' +
@TempDBFirstLogicalName +
CONVERT(VarChar(3), @StartID)
SET @SQL_Command = @SQL_Command +
''', FILENAME = N''' +
@TempDBFirstPhysicalPath +
@TempDBFirstLogicalName +
CONVERT(VarChar(3), @EndID)
SET @SQL_Command = @SQL_Command +
'.ndf' +
''', SIZE = ' +
CONVERT(VarChar(10), @size) +
'MB, FILEGROWTH = '
SET @SQL_Command = @SQL_Command +
CONVERT(VarChar(10), @growth) +
'MB)'
--PRINT @SQL_Command
EXEC (@SQL_Command)
SET @TempDBMissingDataFileCount = @TempDBMissingDataFileCount - 1
END
END
--
-- Set TempDB Files in their correct folders
--
-- Create the TempDB_Data folder on the target drive if needed
SET @SQL_Command = 'MD ' + @Drive + ':\TempDB_Data'
EXEC Master..xp_CmdShell @SQL_Command
-- Create the TempDB_Log folder on the target drive if needed
SET @SQL_Command = 'MD ' + @Drive + ':\TempDB_Log'
EXEC Master..xp_CmdShell @SQL_Command
-- Move the TempDB MDF file to the folder on the target drive
SET @SQL_Command = 'ALTER DATABASE TempDB MODIFY FILE (Name = TempDev, Filename = ' + CHAR(39) + @Drive + ':\TempDB_Data\TempDev.MDF' + CHAR(39) + ')'
EXEC (@SQL_Command)
-- Move the TempDB LDF file to the folder on the target drive
SET @SQL_Command = 'ALTER DATABASE TempDB MODIFY FILE (Name = TempLog, Filename = ' + CHAR(39) + @Drive + ':\TempDB_Log\TempLog.LDF' + CHAR(39) + ')'
EXEC (@SQL_Command)
-- Move the TempDB NDF(s) file to the folder on the target drive
SET @StartID = 2
WHILE @StartID <= @NbrProcs
BEGIN
SET @SQL_Command = 'ALTER DATABASE TempDB MODIFY FILE (Name = TempDev' + CONVERT(VarChar,@StartID) + ', Filename = ' + CHAR(39) + @Drive + ':\TempDB_Data\TempDev' + CONVERT(VarChar,@StartID) + '.NDF' + CHAR(39) + ')'
EXEC (@SQL_Command)
SET @StartID = @StartID + 1
END
.