Move TempDB data and log files to new location

Step 1: Identify the current location of TempDB Data and Log Files.

Use master

GO

SELECT

name AS [LogicalName]

,physical_name AS [Location]

,state_desc AS [Status]

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

Step 2: Change the location of TempDB Data and Log files using ALTER DATABASE.

Execute below query to change file path for tempdb data & log files.

Please make sure there are no active transactions on TempDB.

ALTER DATABASE Tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'T:\TempDB\tempdb.mdf');

GO

Note: If you have multiple data files perform above step for all those files.

ALTER DATABASE Tempdb

MODIFY FILE (NAME = templog, FILENAME = 'T:\TempDB\templog.ldf');

GO

Step 3: Restart MSSQL Services. There would be an outage of 5 minutes at maximum.

Check the job activity monitor for any active jobs running before restarting.

Step 4: Verify the File Change.

Check if new data files were as required on new path by below query.

Use master

GO

SELECT

name AS [LogicalName]

,physical_name AS [Location]

,state_desc AS [Status]

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

Step 5: Go to the original path and delete all old data and log files for tempdb.