Interview Question of the Week #048 – How to Move TempDB to Another Drive?

Question: How do I move TempDB to another drive? Update the intended file locations with ALTER DATABASE MODIFY FILE, then restart the SQL Server instance during planned downtime. TempDB is recreated at the new locations when the service starts.

A candidate was surprised when this question came up in an interview. It is worth knowing because a drive can run out of space, and a different storage location may better suit TempDB’s workload. The SQL is short. The preparation is what makes the move succeed.

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

-- Planned maintenance templates only. Verify logical names, folders,
-- free space and SQL Server service-account permissions first.
-- USE master;
-- ALTER DATABASE tempdb MODIFY FILE
-- (NAME=tempdev,FILENAME='D:\SQLData\TempDB\tempdb.mdf');
-- ALTER DATABASE tempdb MODIFY FILE
-- (NAME=templog,FILENAME='E:\SQLLog\TempDB\templog.ldf');
-- Repeat for every additional data file that you intend to move.
-- Stop and start the SQL Server instance during approved downtime.
-- Then rerun the inventory above and inspect actual tempdb files:
-- USE tempdb;
-- SELECT name,type_desc,physical_name FROM sys.database_files;

Inventory every file before changing paths

The original example used tempdev and templog, but your instance may have several TempDB data files. Read their actual logical names and full paths. The two commented commands illustrate the syntax; they are not a complete move for an instance with additional files.

Create the destination folders, check free space, and grant the SQL Server Database Engine service account the required access. A path that works for your Windows account may still fail when the service starts. Keep the original locations and a recovery procedure available until the restart succeeds.

The restart is an instance operation

Changing FILENAME updates the configured location. TempDB continues using its existing files until SQL Server restarts. You do not manually copy its current data and log files: the service recreates TempDB at startup.

After the restart, verify the configured paths, the files used by TempDB, the error log and a normal application connection. Only then remove confirmed unused old files.

A different drive letter does not necessarily mean different physical storage or faster I/O. Choose the storage based on the actual workload and underlying device. Moving TempDB to another filegroup is not the reason for parallel work; TempDB uses its primary filegroup, and file placement and allocation contention are separate decisions.

Microsoft’s TempDB relocation procedure includes permissions, restart and verification steps.

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