Interview Question of the Week #027 – Move TempDB from One Drive to Another Drive When Low Disk Space

Practical interview questions are my favorites because they show how a DBA thinks before typing a command. I was asked what to do when the tempdb log fills the drive. My old answer jumped directly to moving the file. Today I would first find out why the log is growing, then move it if the drive truly needs more capacity.

Question: How do you move tempdb from one drive to another when disk space is low?

Answer: Confirm which tempdb file and volume are under pressure. Check the cause of growth. If a move is still appropriate, change every affected file’s configured path, restart SQL Server during an approved window, and verify the new physical paths.

Check the problem before moving a file

A full log may be retained by an active transaction. Moving that log to a larger volume buys space but does not end the transaction. A data file can grow because of temporary tables, sort or hash work, or version store activity. Start with the file inventory and the SQL Server error, and ask the Windows administrator about free space on the actual volume.

SELECT name, type_desc, physical_name,
       size * 8.0 / 1024 AS CurrentSizeMB,
       max_size, growth, is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
ORDER BY file_id;

DBCC OPENTRAN (N'tempdb');

DBCC OPENTRAN is a clue for a retained log, not a license to kill a session. Work with the owner of the transaction. Verify file growth settings and free volume capacity; do not blindly remove the file size limit or rely on autogrowth as a capacity plan.

The original file-inventory command, USE tempdb; EXEC sys.sp_helpfile;, is another way to see the current files. After a path change is configured but before restart, sys.master_files can show the new configured path while tempdb.sys.database_files still shows the running file path.

Make the move as a planned change

Record all logical file names from the query above. Create the destination folders first and grant the SQL Server Database Engine service account the required permissions. The two names below are common defaults; use your own inventory. Change the sample paths to approved paths on your server.

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

Repeat MODIFY FILE for every tempdb data and log file that needs to move. These statements change the configured paths; the running instance continues using the old files until restart. During the maintenance window, restart the SQL Server service. SQL Server creates the tempdb files at the new paths. If the service account cannot use a destination, the instance may fail to start, so check permissions before the restart.

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

Confirm that the paths changed and that the instance and applications behave normally. Only after verification should the old files and folders be considered for cleanup under the server’s recovery procedure.

The earlier explanation is still available in TempDB Is Full: Move TempDB from One Drive to Another Drive.

My original tempdb move demonstration shows the historical workflow. You can also connect with me on LinkedIn.

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