How to Move TempDB to New Drive in SQL Server?

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:-

1. Identify the location of TempDB Data and Log Files
2. Change the location of TempDB Data and Log files using ALTER DATABASE
3. Stop and Restart SQL Server Service
4. Verify the File Change
5. 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.

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

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.

Read more: http://www.mytechmantra.com/LearnSQLServer/How-to-Move-TempDB-to-New-Drive-in-SQL-Server/#ixzz3ck1UK2B8
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook