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.