Skip to main content

MsSQL - Move TempDB To Another Drive

Sometimes as a database administrator, you need to move the TempDB database and log files to a new hard drive.

This happens for example if you have installed SQL Server on C:\ and you no longer have space to process your queries. So it is necessary to move TempDB to a disk with more free space.

This article explains all the steps to move TempDB files.

Steps to move TempDB and log files to new location

Here are the steps to move SQL Server temporary database :

  • Identify the location of TempDB data and log files
  • Change the location of TempDB data and log files using ALTER DATABASE
  • Stop and restart the SQL Server service
  • Check path change
  • Delete old TempDB as well as .mdf and .ldf files

Identify the location of TempDB data and log files

In the query window of SQL Server Management Studio, run the script below to identify the location of the TempDB data and log file:

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

This query shows us the presence of the TempDB in the default folder of the SQL Server installation :

TempDB in the default folder

Once you have identified the location of the TempDB files, the next step will be to create folders on the new hard drive where you want to store the TempDB data and log file.

However, you must ensure that the new location where the TempDB files are stored is accessible by SQL Server. That is, you must ensure that the account under which the SQL Server service is running has read and write permissions to the folder where the files are stored.

Change Location of TempDB Data Files and Log Files Using ALTER DATABASE

Run the ALTER DATABASE command below to change the TempDB data and log file location in SQL Server:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO

Tip if you have several tempDB files

It happens that sometimes we have several tempDB databases to move, as shown in our example above. Instead of doing several copy / paste, a small script to generate all TempDB move requests:

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''Z:\MSSQL\DATA\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');

The result is:

generate all TempDB move requests

A simple copy / paste of all the queries to execute them at once!

Stop and restart the SQL Server service

Stop and restart the instance of SQL Server for the changes to take effect.

Verify Changed Location of TempDB Data Files and Log Files

All you have to do is re-execute the very first query. Results:

Delete old tempdb.mdf and templog.ldf files

The final step will be to delete the tempdb.mdf and templog.ldf files from the original location. So just go there to the location and delete them with the DELETE key on your keyboard or with the right mouse button.

Note: SQL Server does not support moving the TempDB database using backup/restore and using database detach methods. The only way to do this is by Transact SQL code as shown above.

Back to top