Moving TempDB

Moving TempDB to another drive or folder is a routine job, but there are a couple of traps. The steps:

  • Identify the Current Location:
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
  • Specify the New Location:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'new_path\tempdb.mdf');
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'new_path\templog.ldf');

Most servers have several tempdb data files (tempdev, temp2, temp3 and so on), and every one needs moving. This generates the statements for all of them; replace new_path first:

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(name)
     + ', FILENAME = N''new_path\'
     + RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name)) - 1) + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
  • Restart SQL Server:
    • Stop the SQL Server service.
    • There’s no need to copy the tempdb files. SQL Server creates them at the new location when it starts. Delete the old files once it’s running.
    • Restart the SQL Server service.
  • Verify the Change:
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

Before you restart, check the SQL Server service account has permissions on the new folder

From my SQL Server DBA handbook.