/*

Author: Michael Petri

Original link: https://gist.github.com/FlogDonkey/3b9851f1c13b94f3f2d26b4ab5b49de0

*/

/*

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 @debug BIT = 1;

DECLARE @DriveSizeGB int = 40;

DECLARE @FileCount int = 9;

DECLARE @RowID int;

DECLARE @FileSize varchar(10);

DECLARE @DrivePath nvarchar(512) = N'T:\' + @@SERVICENAME + '\';

/* 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 N'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + N'],' + N' FILENAME = ''' + @DrivePath + f.name

+ CASE

WHEN f.type = 1 THEN N'.ldf'

ELSE N'.mdf'

END + N''', SIZE = ' + @FileSize + ');'

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 N'ALTER DATABASE tempdb ADD FILE (NAME = [temp' + CAST(@RowID AS VARCHAR) + N'],' + N' FILENAME = ''' + @DrivePath + N'temp'+ CAST(@RowID AS varchar(70)) + N'.mdf''' + N', SIZE=' + @FileSize + N');';

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;

IF @debug = 1

PRINT(@WorkingSQL)

ELSE

EXEC (@WorkingSQL);

SET @RowID = @RowID - 1;

END;