SQL DBA Tutorial

How to Move TempDB from one drive to another drive

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. 

           Use Tempdb
           go
           sp_helpfile
           go

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

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. 

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. 

         Use Tempdb
         go
         sp_helpfile
         go

 

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.

Thank you for subscribing.
Something went wrong.
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.

Exit mobile version