Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Moves SQL TempDB files to designated location, and sizes them appropriately. If there are multiple instances hosted on the same SQL Server, divide the drive size by the instance count, and create folders for each instance name on the destination drive.
/*
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
,@RowID INT
,@FileSize VARCHAR(10)
,@DrivePath VARCHAR(100) = '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 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],' + ' FILENAME = ''' + @DrivePath + f.name
+ CASE
WHEN f.type = 1 THEN '.ldf'
ELSE '.mdf'
END + ''', 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 'ALTER DATABASE tempdb ADD FILE (NAME = [temp' + CAST(@RowID AS VARCHAR) + '],' + ' FILENAME = ''' + @DrivePath + 'temp'+ CAST(@RowID AS VARCHAR)+'.mdf''' + ', SIZE='+@FileSize+');'
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
EXEC (@WorkingSQL)
SET @RowID = @RowID - 1
END
@AndyDBA

This comment has been minimized.

Copy link

@AndyDBA AndyDBA commented Jun 10, 2019

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

@brokenthorn

This comment has been minimized.

Copy link

@brokenthorn brokenthorn commented Jul 4, 2019

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

@bibe74

This comment has been minimized.

Copy link

@bibe74 bibe74 commented Oct 18, 2019

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

@JLTN19

This comment has been minimized.

Copy link

@JLTN19 JLTN19 commented Oct 21, 2020

This is a great script - thanks for sharing!

@JLTN19

This comment has been minimized.

Copy link

@JLTN19 JLTN19 commented Oct 21, 2020

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.

@bibe74

This comment has been minimized.

Copy link

@bibe74 bibe74 commented Oct 22, 2020

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.