Move Temp DB SQL Server

A common best practice for SQL Server DBA’s is to have Temp DB files stored on a separate drive, especially ensuring that it’s not on the C:\ drive and sharing with the OS.

This is because Temp DB can grow very large, very quickly. The growth of Temp DB Files depends on the workload/queries happening on your SQL Server. Temp DB growth can happen on both the data and log files.

This post is a tutorial on how to move all Temp DB files from one volume to another in SQL Server.

Downtime is required for this action, so we should ensure we have a planned maintenance window for this work.

Move Temp DB Files MSSQL

First, I’m going to use sp_helpfile to view logical Temp DB names.

-- move temp db files sql server
USE temp db;
GO

-- check physical names and attributes for current database
exec sp_helpfile;

We can see this SQL Server Instance has 8x temp_db data files and one temp_db log file.

Next, run the following script to generate the ALTER statements required to move these files. You’ll need to amend the FILENAME parameter to the new location.

Grab the SQL output from here and then run the ALTER DATABASE commands.

-- move temp_DB database to new location on disk
-- (amend temp_db to actual db name)
ALTER DATABASE temp_db MODIFY FILE (NAME = [tempdev], FILENAME = 'D:\mssql_temp_db\tempdev.mdf');
ALTER DATABASE temp_db MODIFY FILE (NAME = [templog], FILENAME = 'D:\mssql_temp_db\templog.ldf');
ALTER DATABASE temp_db MODIFY FILE (NAME = [temp2], FILENAME = 'D:\mssql_temp_db\temp2.mdf');
ALTER DATABASE temp_db MODIFY FILE (NAME = [temp3], FILENAME = 'D:\mssql_temp_db\temp3.mdf');
ALTER DATABASE temp_db MODIFY FILE (NAME = [temp4], FILENAME = 'D:\mssql_temp_db\temp4.mdf');
ALTER DATABASE temp_db MODIFY FILE (NAME = [temp5], FILENAME = 'D:\mssql_temp_db\temp5.mdf');
ALTER DATABASE temp_db MODIFY FILE (NAME = [temp6], FILENAME = 'D:\mssql_temp_db\temp6.mdf');
ALTER DATABASE temp_db MODIFY FILE (NAME = [temp7], FILENAME = 'D:\mssql_temp_db\temp7.mdf');
ALTER DATABASE temp_db MODIFY FILE (NAME = [temp8], FILENAME = 'D:\mssql_temp_db\temp8.mdf');

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.

The above output message informs that we have to restart the SQL Service for this change to take effect. So let’s do that now.

Once done, we can check the active temp db files in our new directory.

And verify in MSSQL by using exec sp_helpfile as above in this demo, or by opening the database Shrink File window in SSMS.

Lastly, remember to delete the old Temp DB files from where you moved them from.

I hope this guide provided you with some insight and that your change goes without issues! Feel free to check out my Database Admin tips tag for more random posts from a SQL DBA.