Interview Question of the Week #015 – How to Move TempDB to Different Drive

People often ask me how to move tempdb to another drive after seeing its log fill up. The move itself is straightforward. The more important question is whether the new drive solves the problem that filled the log.

Question: How do you move tempdb to a different drive?

Answer: Record every logical data and log file, set each file’s new path with ALTER DATABASE ... MODIFY FILE, and restart the SQL Server service. tempdb is recreated at the new location during startup; you do not copy its old files.

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

Use the names returned on your own instance. The following shows the syntax for two common logical names, not a complete script for an instance with additional data files:

USE master;
GO
ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = N'E:\SQLData\tempdb.mdf');
ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = N'F:\SQLLog\templog.ldf');
GO

Create the destination folders first and grant the SQL Server service account access. Include every tempdb file in the plan. After a scheduled service restart, run the first query again and confirm all paths before setting old files aside. A wrong path or missing permission can prevent the instance from starting.

If the original symptom was “The log file for database ‘tempdb’ is full,” moving the log to a larger drive may provide capacity, but it does not explain why log space could not be reused. Check active transactions and the workload that generated the log. The original read-only diagnostic is DBCC OPENTRAN(N'tempdb');. It reports the oldest active transaction when one exists; it does not identify every consumer of tempdb space. Changing a drive letter also does not guarantee a performance gain if both letters point to the same storage.

I discussed the original problem in more detail in TempDB is Full: Move TempDB from one drive to another.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.