How to Move TempDB from one drive to another drive

In this article we will explain step by step how to move tempDB files from one drive to another drive in SQL Server. However, for the changes to come into effect you must restart the SQL Services. 

  • Take backup of all database(System and user databases).
  • Need to identify the location of TempDB data and log file location. Open Query Analyzer and connect to your server.Run below script to get the names and files location used for TempDB.

           Use Tempdb
           go
           sp_helpfile
           go

On my server, The results where shown in SQL Server Management Studio as below.

How to Move TempDB from one drive to another drive

Once you have identified the logical name and location of TempDB files, then the next step will be to create the folders on the new drive and provide full permissions(read and write) on the folder where the files are stored. 

In this example , I am going to create new folders in E:\ drive and providing full permissions. 

Create folder > Right click on folder> click properties> click sharing> click share> Enter Everyone> Click Add>  change Read/Write > Click share> Click Apply. 

Tempdb File movement

  • Execute the below ALTER DATABASE commend to change the location of TempDB data and log file in SQL Server.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘D:\TempDB_Files\tempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘D:\TempDB_Files\templog.ldf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp2, FILENAME = ‘D:\TempDB_Files\tempdb_mssql_2.ndf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp3, FILENAME = ‘D:\TempDB_Files\tempdb_mssql_3.ndf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp4, FILENAME = ‘D:\TempDB_Files\tempdb_mssql_4.ndf’);
GO

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

TempDB file movement

  • Stop and restart the SQL Server Instance for the changes to come into effect.

SQL SERVER DBA

  • Execute the below scrip to verify whether TempDB files are residing in the new location.

         Use Tempdb
         go
         sp_helpfile
         go

SQL DBA

  • Check the All database consistency.
  • The final step will be to delete the old TempDb files from the Old location.

 

How to Prepare POA for TempDB file movement

SQL Sever 2012 Setup Error Rule ‘Setup Account Privileges’ Failed

How to Uninstall SQL Server 2012 Instance

 

 

Get SQL Dba Tutorial Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to MailChimp ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Add Comment