How To Move TempDB Files

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.

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

• Database Health Monitor – I/O by Drive Report
• Database Health Monitor – I/O by Database Report

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!