SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Move TempDB to New Drive in SQL Server

There are times when as a DBA you find the need to move TempDB Data and Log Files to a new Drive. This article explains all the steps you need to follow to move TempDB files.


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

This article outlines the step to Change the Location of TempDB Data and Log files in SQL Server.

Move TempDB / Change TempDB Location / Move TempDB to Another Drive / Move TempDB to New Drive / How to Move SQL Server TempDB 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

In a Failover Cluster Instance to Move TempDB read, How to Configure TempDB on Local Disk in SQL Server 2012/2014 Failover Cluster to Improve Performance



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.

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.


Recommendation: Top 10 Trending SQL Server Tips for DBAs and Developer.

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

1 comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement