New and improved Move TempDB script. Captures xp_cmdshell value before execution and resets it post-execution. Allows for dynamic drive allocation in case of company policy of leaving some set percent of drive available for future growth. Also includes improved error handling and validation, as well as a @debug mode, allowing us to run the scrip…

Move TempDB.sql

/*
	Snippet is nuts and bolts for creating/moving to an isolated tempdb drive.
	After you run this, SQL Server must be restarted for it to take effect
*/
DECLARE @DriveSizeGB				 INT		   = 40
		,@FileCount					 INT		   = 9
		,@InstanceCount				 TINYINT	   = 1
		,@VolumeBuffer				 DECIMAL(8, 2) = .8 /* Set to amount of volume TempDB can fill. */
		,@RowID						 INT
		,@FileSize					 VARCHAR(10)
		,@InitialXPCmdshellValue	 SQL_VARIANT
		,@CreateDirectoryIfNotExists BIT		   = 1	/* Flag to have SQL create the directories if they don't exist */
		,@GreenLight				 BIT		   = 1	/* Flag for proceeding once directories are created, or halting if something is wrong */
		,@xp_cmd					 VARCHAR(255)
		,@xp_cmd_message			 VARCHAR(255)
		,@DrivePath					 VARCHAR(100)  = 'T:\' + @@SERVICENAME + '\'
		,@Debug						 BIT		   = 1;

/* Get Initial xp_cmdshell value */
SELECT	@InitialXPCmdshellValue = c.value
FROM	sys.configurations AS c
WHERE	c.name LIKE '%xp_cmd%';

/* Placeholder for xp_cmdshell output */
DECLARE @Output TABLE
(
	Column1 VARCHAR(MAX)
);

IF @InitialXPCmdshellValue = 0
BEGIN
	/* Enable xp_cmdshell */
	EXEC sys.sp_configure 'Show Advanced Options', 1;

	RECONFIGURE;

	EXEC sys.sp_configure 'xp_cmdshell', 1;

	RECONFIGURE;
END;

/* Sanitize path */
IF (RIGHT(@DrivePath, 1) <> '\')
BEGIN
	SET @DrivePath = @DrivePath + '\';
END;

IF OBJECT_ID('tempdb..#DataResults') IS NOT NULL
BEGIN
	DROP TABLE #DataResults;
END;

/* Check to ensure directory is valid and accessible by SQL Service */
CREATE TABLE #DataResults
(
	FileExists		 INT
	,IsDirectory	 INT
	,ParentDirExists INT
);

INSERT INTO #DataResults
EXEC master..xp_fileexist @DrivePath;

/************************/
/* Path Validation */
/************************/

/* If specified directory not exists and @CreateDirectory parameter is FALSE */
IF NOT EXISTS (
				  SELECT	1
				  FROM		#DataResults AS r
				  WHERE		r.IsDirectory = 1
			  )
   AND	@CreateDirectoryIfNotExists = 0
BEGIN
	SELECT @GreenLight =  0;

	SELECT	'Data directory not exists and @CreateDirectoryIfNotExists is FALSE' AS Message
			,@GreenLight AS GreenLight;
END;

/* If specified directory not exists and @CreateDirectory parameter is TRUE */
ELSE IF NOT EXISTS (
					   SELECT	1
					   FROM		#DataResults AS r
					   WHERE	r.IsDirectory = 1
				   )
		AND @CreateDirectoryIfNotExists = 1
BEGIN
	SET @xp_cmd = 'mkdir ' + @DrivePath;

	INSERT INTO @Output
	(
		Column1
	)
	EXEC master..xp_cmdshell @xp_cmd;

	/* Return message from xp_cmdshell */
	SELECT	TOP 1
			@xp_cmd_message = o.Column1
	FROM	@Output AS o
	WHERE	o.Column1 IS NOT NULL;

	/* If an error was returned, set GreenLight to FALSE and return message*/
	IF @xp_cmd_message IS NOT NULL
	BEGIN
		SET @GreenLight = 0;

		SELECT	'Problem with path' AS Message
				,@xp_cmd_message AS ErrorMessage
				,@GreenLight AS GreenLight;
	END;
END;

/* Reduce available space if requried by company policy */
IF ISNULL(@VolumeBuffer, 0) > 0
BEGIN
	/* Allocates 80% of volume for TempDB */
	SELECT	@DriveSizeGB = (@DriveSizeGB / @InstanceCount) * .8;
END;

/* Converts GB to MB */
SELECT	@DriveSizeGB = @DriveSizeGB * 1000;

/* Splits size by the nine files */
SELECT	@FileSize = @DriveSizeGB / @FileCount;

/* Table to house requisite SQL statements that will modify the files to the standardized name, and size */
DECLARE @Command TABLE
(
	RowID	 INT IDENTITY(1, 1)
	,Command NVARCHAR(MAX)
);

INSERT INTO @Command
(
	Command
)
SELECT	'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],' + ' FILENAME = ''' + @DrivePath + f.name
		+ CASE
			  WHEN f.type = 1 THEN '.ldf'
			  ELSE '.mdf'
		  END + ''', SIZE = ' + @FileSize + ', FILEGROWTH=512);'
FROM	sys.master_files AS f
WHERE	f.database_id = DB_ID(N'tempdb');

SET @RowID = @@ROWCOUNT;

/* If there are less files than indicated in @FileCount, add missing lines as ADD FILE commands */
WHILE @RowID < @FileCount
BEGIN
	INSERT INTO @Command
	(
		Command
	)
	SELECT	'ALTER DATABASE tempdb ADD FILE (NAME = [temp' + CAST(@RowID AS VARCHAR) + '],' + ' FILENAME = '''
			+ @DrivePath + 'temp' + CAST(@RowID AS VARCHAR) + '.mdf''' + ', SIZE=' + @FileSize + ', FILEGROWTH=512);';

	SET @RowID = @RowID + 1;
END;

/* Execute each line to process */
WHILE @RowID > 0
BEGIN
	DECLARE @WorkingSQL NVARCHAR(MAX);

	SELECT	@WorkingSQL = Command
	FROM	@Command
	WHERE	RowID = @RowID;

	PRINT @WorkingSQL;

	IF @Debug = 0
	   AND	@GreenLight = 1
	BEGIN
		EXEC (@WorkingSQL);
	END;

	SET @RowID = @RowID - 1;
END;

IF @InitialXPCmdshellValue = 0
BEGIN
	/* Enable xp_cmdshell */
	EXEC sys.sp_configure 'Show Advanced Options', 1;

	RECONFIGURE;

	EXEC sys.sp_configure 'xp_cmdshell', 0;

	RECONFIGURE;
END;

IF OBJECT_ID('tempdb..#DataResults') IS NOT NULL
BEGIN
	DROP TABLE #DataResults;
END;

Thank you for this script, it just works, brilliant!

Featured by Brent Ozar Unlimited® Weekly Links, June 10th Edition. Congrats!

Brilliant script, really! I did a very minor tweak, changing the WHERE clause at line 48 from "WHERE RowID = @ROWID" to "WHERE RowID = @FileCount - @ROWID", just for altering/adding the files in the natural order

This is a great script - thanks for sharing!

Brilliant script, really! I did a very minor tweak, changing the WHERE clause at line 48 from "WHERE RowID = @ROWID" to "WHERE RowID = @FileCount - @ROWID", just for altering/adding the files in the natural order

I tried your modification, and I kept ending up with 1 file short every time, even though they were created in ASC vs DESC order. I tweaked your mod to: "WHERE RowID = (@FileCount - @ROWID) + 1". This created the correct # of files as well as created them in an natural order.

Brilliant script, really! I did a very minor tweak, changing the WHERE clause at line 48 from "WHERE RowID = @ROWID" to "WHERE RowID = @FileCount - @ROWID", just for altering/adding the files in the natural order

I tried your modification, and I kept ending up with 1 file short every time, even though they were created in ASC vs DESC order. I tweaked your mod to: "WHERE RowID = (@FileCount - @ROWID) + 1". This created the correct # of files as well as created them in an natural order.

Thank you, @JLTN19! I completely missed the file count mismatch!

Hi,

I have amended the script to my tempdb location however whenever I execute it I'm unable to start my SQL server instance - can anyone assist please?

With the instance stopped, delete all tempdb files in your location, then start the instance. The instance likely cannot create the files it believes needs due to existing files, e.g. insufficient disk space.

Brilliant script, really! I did a very minor tweak, changing the WHERE clause at line 48 from "WHERE RowID = @ROWID" to "WHERE RowID = @FileCount - @ROWID", just for altering/adding the files in the natural order

I tried your modification, and I kept ending up with 1 file short every time, even though they were created in ASC vs DESC order. I tweaked your mod to: "WHERE RowID = (@FileCount - @ROWID) + 1". This created the correct # of files as well as created them in an natural order.

Thanks for the comment! I've added your modification into the code 🥇

Michael, thank you very much. Yes you're correct, due to the existing files it didn't have space to create new tempdb files. Many thanks Matthew

…

On Thu, 15 Apr 2021, 17:08 Michael Petri, ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;} a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;} .MsoChpDefault {mso-style-type:export-only;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1 {page:WordSection1;} -->With the instance stopped, delete all tempdb files in your location, then start the instance. The instance likely cannot create the files it believes needs due to existing files, e.g. insufficient disk space. Sent from Mail for Windows 10 From: matthewellis90Sent: Monday, April 12, 2021 5:42 AMTo: FlogDonkeyCc: Michael Petri; AuthorSubject: Re: FlogDonkey/Move ***@***.*** commented on this gist.Hi,I have amended the script to my tempdb location however whenever I execute it I'm unable to start my SQL server instance - can anyone assist please?—You are receiving this because you authored the thread.Reply to this email directly, view it on GitHub, or unsubscribe. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://gist.github.com/3b9851f1c13b94f3f2d26b4ab5b49de0#gistcomment-3707533>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AM6M6WHYBIB23RHMXY2OBPLTI4FRHANCNFSM4HWRQBTQ> .

Hi,

The parameter @VolumeBuffer doesn't look to be used in the Drive size calculation. It looks to be hardcoded at .8

Should the following line be changed from:
SELECT @DriveSizeGB = (@DriveSizeGB / @InstanceCount) * .8;
To:
SELECT @DriveSizeGB = (@DriveSizeGB / @InstanceCount) * @VolumeBuffer;