How to Move TempDB to New Drive in SQL Server

Introduction

This article explains the steps you must follow to move TempDB database from one drive to another in SQL Server. However, for the changes to come into effect you must restart SQL Server Service.

Overview of Steps to move TempDB data and log files to new location are:-

• Identify the location of TempDB Data and Log Files
• Change the location of TempDB Data and Log files using ALTER DATABASE
• Stop and Restart SQL Server Service
• Verify the File Change
• Delete old tempdb.mdf and templog.ldf files

Identify the location of TempDB Data and Log Files

In the New Query window of SQL Server Management Studio, execute the below-mentioned script to identify the location of 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

Once you have identified the location of TempDB files, then the next step will be to create the respective folders on the new drive where you would like to store the TempDB data and log file. However, you need to make sure that the new location where the TempDB files are stored is accessible by SQL Server. i.e., you need to ensure that the Account under which SQL Server Service is running has read and write permissions on the folder where the files are stored.

Top Trending SQL Server DBA and Developer Articles and Tips

• SQL Delete Duplicate Rows from a SQL Table in SQL Server
• New to SQL Server? Get Started and Learn Transact-SQL
• SQL Server Tutorial: T-SQL Enhancements in SQL Server
• SQL Server Tutorial: Database Backups with Examples
• How to Configure Contained Databases Feature, Create Contained Databases, Create Access to Contained Databases, and Connect to Contained Databases.
• How to Backup, Restore, Synchronize, Attach, Detach, Automate Backup, and Perform DBCC Check of Analysis Services Databases.
• How to Fix PowerShell Execution Policy Error in Windows Server
• How to Repair Suspect Database in SQL Server
• How to Enable Dedicated Administrator Connection in SQL Server
• How to Configuring Database Instant File Initialization Feature of SQL Server
• How to Backup and Restore Resource Database in SQL Server
• How to Start SQL Server in Single User Mode?
• How to Start SQL Server with Minimal Configuration
• How to Detect Virtual Log Files in SQL Server Transaction Log File
• How to Identify the Location of Resource Database in SQL Server
• How to Disable an Index in SQL Server
• How to Monitor Transaction Log File Usage in SQL Server
• How to Connect to SQL Server When all System Administrators are Locked Out
• FORMAT SQL Server Dates Using FORMAT Function in SQL Server

Change the location of TempDB Data and Log files using ALTER DATABASE

Execute the below ALTER DATABASE command to change the location of TempDB Data and Log file 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

Once the above script has executed successfully you will receive a message to restart SQL Server Service for the changes to come into effect.

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

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

Stop and Restart SQL Server Service

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

Verify the File Change

Execute the below TSQL to verify whether TempDB Data and Log files are residing in the new location.

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

Delete old tempdb.mdf and templog.ldf files

The final step will be to delete the tempdb.mdf & templog.ldf files from the original location.

Important Note: SQL Server doesn’t support moving TempDB Database using backup/restore and by using detach database methods.

Error Message Received when you try Backup and Restore Method

Msg 3147, Level 16, State 3, Line 1 
Backup and restore operations are not allowed on database tempdb. 
Msg 3013, Level 16, State 1, Line 1 
BACKUP DATABASE is terminating abnormally.

Error Message Received when you try Detach Method

Msg 7940, Level 16, State 1, Line 1 
System databases master, model, msdb, and tempdb cannot be detached.

Conclusion

This article explains the steps you must follow to move TempDB database from one drive to another in SQL Server.