Moving TempDB database to a different location:

As an administrator, sometimes it requires to move the System database to different location for various reasons like freeing up the space on drive, for improving the performance of the database, reducing the load on the drive, etc.

Now I am also facing the same issue, I need to free up the space on my C drive. Therefore, I am planning to move the System database. Today I tried in moving the Tempdb database to a different drive.

Before going with the process on moving the Tempdb database, I would like to discuss few things that are recommended just like prerequisites.

1. The source and destination drives should be of same type or format. Ex.: Fat32, NTFS, etc.
2. Make sure that the destination drive is not compressed. If it compressed already, you can uncompress by going to the properties of the drive.

Now let us go with process on moving Tempdb to a different location.

Data files of Tempdb are stored in the default location on C drive (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).

I want to move these files to F drive (F:\SystemDatafiles).

1. Execute the following to see the properties of Tempdb like location, size, etc.

SP_HELPDB TempDB

2. Now execute the following to change the location of data files of TempDB database.

3. Restart the Services of SQL Server.

4. Now check the properties of Tempdb like location, size, etc. using the following.

SP_HELPDB TempDB

Now you can find the modified locations.

This is the way we change the location of TempDb database.

In the next post, you can find more on changing the location of other system databases.