How to move tempdb from default location to other location

Hello friends, today we are going to learn how we can move tempdb (system database) from its default location to some other location.

Reason for moving tempdb:- There could be two reason of moving tempdb from default location to some other location, which are following.

• If tempdb has grown up to great extent and existing drive has not enough space.
• To improve database disk read, If we have another file group which is residing on a different physical drive.

Steps to be performed to move tempdb:-

First we will check the logical file of tempdb by running below stored procedure in management studio.

sp_helpfile

In the above screenshot , you can see that I have selected “tempdb” (circle 1) and run the system stored procedure “sp_helpfile” and as result we are getting two rows in the result window. Column “Name” is indicating the logical file name of tempdb and column “Filename” is indicating the location of physical files of tempdb. Currently physical file location for tempdb is below:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\tempdb.mdfàdata file location

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\templog.ldfàlog file location

Now we will run below query to move the database from one drive to another drive.

Before running the below query I have created a folder “tempdb” in “D” drive, which is going to be the new location of physical files for tempdb.

Use master

Alter database tempdb

modify file (name=tempdev,Filename='D:\tempdb\tempdb2008.mdf')

go

Alter database tempdb

modify file (name=templog,Filename='D:\tempdb\tempdb2008.ldf')

Once the query is executed, we have to restart the SQL service to generate the new physical files for tempdb at the new location. After restarting the service, we will run the system stored procedure “Sp_helpfile” to check the physical file location for tempdb. In the below screenshot, you can see that the physical file location has been changed.