How to Move TempDB to a different Drive or Folder

Sometimes you’ll need to move the TempDB files to a different drive or folder. It’s a pretty simple operation and I’ll provide a script for you to use.

Typically to move the TempDB files, you would do it in 3 stages:

1. Set the new location in SQL Server, by running a command in SSMS
2. Restart SQL Server instance. This will recreate the database files in the new location
3. Delete the old tempdb files in the old location to keep things tidy.

The command to do it is the Alter Database tempdb Modify File command, which I showed in one of my previous posts – Moving Database Files, It Ain’t That Hard. For example:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\NewLocation\tempdb.mdf') 

Often though, SQL Server instances have many tempdb files and you usually want to move them all. To quickly generate these Alter Database commands, you can run the below code. Ensure to update the @new_path variable with the new directory where you want tempdb to be living. If the path doesn’t exist yet, well, you should do that first.

-- Ensure to include the trailing "\"
DECLARE @new_path NVARCHAR(4000) = N'T:\New_File_Path\';

SELECT 
	  N'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(f.[name]) + N', '
    + N'FILENAME = ''' + @new_path + RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name)) -1) 
	+ ''');'
    AS [AlterCommand]
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');

The results are:

You can then copy and paste these results into a new SSMS query window, review them, then run them.

Once you execute these Alter Database commands, you’ll now need to restart the SQL Server instance for it to take affect. Just the SQL Server service is fine. When you do this, SQL Server will recreate the tempdb files in the new location – you don’t need to physically move the files. Check it out by browsing to the new location – all going well, the tempdb files will have been created there.

Lastly, the old tempdb files in the old location will still be sitting there, doing nothing but taking up a whole lot of disk space. Delete them to keep the server tidy. Everyone likes a tidy server.