Moving SQL tempdb to another drive

Today I urgently needed to move tempdb from one drive to another to free up space. So I googled to find a script and found this one by Brent Ozar. It has been added to StackExchange and refined a bit.

DECLARE @newDriveAndFolder VARCHAR(8000);

SET @newDriveAndFolder = 'Z:\YourTempDBfolder';

SELECT [name] AS [Logical Name]
    ,physical_name AS [Current Location]
    ,state_desc AS [Status]
    ,size/1024 AS [Size(MB)]
    ,'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(f.[name])
    + CHAR(9) /* Tab */
    + ',FILENAME = ''' + @newDriveAndFolder + CHAR(92) /* Backslash */ + f.[name]
    + CASE WHEN f.[type] = 1 /* Log */ THEN '.ldf' ELSE '.mdf' END  + ''''
    + ');'
    AS [Create new TempDB files]
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb')
ORDER BY f.[type];

Change the newDriveAndFolder variable and run the script and it will generate the T-SQL statements you need to move them.

ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2] ,FILENAME = ‘F:\MSSQLDATA\temp2.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3] ,FILENAME = ‘F:\MSSQLDATA\temp3.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4] ,FILENAME = ‘F:\MSSQLDATA\temp4.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5] ,FILENAME = ‘F:\MSSQLDATA\temp5.mdf’);

Worked like a charm

/Johan