How To Move TempDB Files

Download PDF

I was asked recently how to move TempDB on SQL Server. The question specifically was how to move the TempDB files to different drives. At that point I realized that I don’t have that posted to my blog, so here we go.

You may have a specific reason to move your TempDB to faster drives. There may be any number of other reasons that may cause you to want to move your TempDB. The most common reason is to reduce the I/O on an existing drive my moving TempDB to a different disk system. A common practice is to move your TempDB onto a Solid State Drive, or a Fusion-IO to get improved performance.

Before moving your TempDB, it might be worth taking a look at the baseline I/O stats. You can use the I/O by Drive Report and the I/O by Database Report in Database Health Monitor to establish baseline I/O numbers before making the change.

Checking the location of TempDB

Just right click on TempDB in SQL Server Management Studio. From the popup menu choose properties, then click on the Files tab. The Path column shows the current location of TempDB.

How to Move TempDB

Move TempDB

Here is a quick script that you can use to Move TempDB, just replace z:\SQL_DATA and y:\SQL_DATA with your own disks and paths.

USE master;
GO
-- Move TempDB to z:\SQL_DATA\ and the log to Y:\SQL_DATA\
ALTER DATABASE tempdb MODIFY
FILE (NAME = tempdev, FILENAME = 'Z:\SQL_DATA\tempdb.mdf');
ALTER DATABASE tempdb MODIFY
FILE (NAME = templog, FILENAME = 'Y:\SQL_DATA\templog.ldf');

This script will work on SQL Server 2005, 2008, 2008R2, 2012, 2014, and 2016. It will probably also work with SQL Server 2000 but I didn’t try it myself.

After making the changes you will need to restart your SQL Instance in order for the changes to be applied.

You will want to check the original location of TempDB, and you may need to delete the original files after the restart of SQL Server.

Related Links

 

Tagged with: , , ,
2 comments on “How To Move TempDB Files
  1. Larry B says:

    This script worked a treat. I might add that you will still need to delete the original Tempdb from the old located.

    Thanks for saving our server!

    Larry B

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.