How to Move TempDB from one drive to another drive

Sasidhar K

4 years ago

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.

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.