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.
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.
- 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.
- Stop and restart the SQL Server Instance for the changes to come into effect.
- Execute the below scrip to verify whether TempDB files are residing in the new location.
Use Tempdb
go
sp_helpfile
go
- 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
