SQL Server 2005: Move tempdb

Posted May 8, 2007 by Rob Rogers in Database

By default, tempdb is placed on the same drive that SQL Server is installed on. This is probably not a desirable result, especially if you have installed SQL Server on the boot drive or on an IDE versus a SATA drive. To move tempdb to a faster drive and improve performance, follow these steps:

1. Open SQL Server Management Studio.

2. Connect to the desired server.

3. Click the New Query button.

4. Copy and paste the following into the query pane:USE master;
MODIFY FILE (NAME = tempdev, FILENAME = '{new location}\tempdb.mdf');
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf');

5. Change {new location} in the pasted code (for both the tempdb.mdf and templog.ldf files) to the path of the new location.

6. Click Execute.

7. Go to the Control Panel and then Administrative Tools. Select Services.

8. Stop and Start SQL Server (MSSQLSERVER).

9. Go back to SQL Server Management Studio and open a new query pane.

10. Copy and paste the following to verify that tempdb has moved to the new location:SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

11. Click Execute.

12. In the physical_name column, you should see the path to the new location.


About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.