Moving TempDB

One of the planned projects in my schedule for the coming financial year is to add some new disks in to one of our SQL servers. As part of this project I will be moving the TempDB database onto a different disk thereby helping to reduce the IO contention on the server. If you want to read more about the benefits of moving TempDB and other best practices then I suggest that you read chapter 7 (Knowing TempDB) in Professional SQL Server 2008 Internals and Troubleshooting, you can read my review about it here.

You may think that you need a server in order to be able to test this, but you can do this on a budget with a bog standard desktop or laptop (as long as it meets SQL Server Express’s minimum requirements) with one physical drive and a USB stick.

First of all insert the USB stick, ensure it’s mapped as a drive and connect to the instance on which you wish to move the  TempDB database then run the following code:

USE master;
GO
SELECT Name, Type_Desc, File_ID, Physical_Name, Size, Growth, Is_Percent_Growth
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

This code provides the current location of the files (make sure you note this down if you are running this as a test as you will want to change it back to the original configuration) as well as the size and growth settings. The reason I am adding the extra size information in the resultset is that we are going to need to restart the instance and when the instance is restarted TempDB will be pre sized to the values shown above. You really need to check to see if these values reflect the current size of the database. You can do this one of two ways.

1. Run the stored procedure sp_SpaceUsed whilst connected to TempDB.
2. Run the Disk Usage report for TempDB in SSMS. To do this you need to right click on TempDB, choose “Reports” then “Standard Reports” and then “Disk Usage”.

I recommend using option 2 if you do not regularly check the size of your databases as the report has a feature allowing you to see recent file growth.

If the pre determined size is smaller than the current size then you really do need to pre size it accordingly. Again this can be done two ways, via the GUI or by the “ALTER TABLE” T-SQL command. Of course you may have the other issue where TempDB is pre sized and it will not fit onto the USB Stick, in this instance you will need to shrink the database files (never thought I would be saying that on a blog post!).

Now we have checked the size of TempDB it is time to move the TempDB database files onto the USB stick. In my case the USB stick has been mapped to drive “J” and I have created a directory SQLTest to hold the files. To move the files you need to use the “ALTER DATABASE” command with the “MODIFY FILE” clause:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'J:\SQLTest\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'J:\SQLTest\templog.ldf');
GO

Depending on the number of files you have in TempDB you will receive a message similar to the following:

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.

The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.

I found that when I ran the first query again on my local MAPS instance (SQL Server 2008 R2 Express), it showed the TempDB database files in the new location with the correct sized files. However Books Online says that this will not happen until the service is restarted. I would still recommend restarting the service just to make sure that everything is OK.

After the service restart you should run the first query to make sure that everything is OK, you will then be safe to delete the old database files to free up space.