SQL Server 2005: Move tempdb

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;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{new location}\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf');
GO

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.

  • sir

    Thanks

  • Kelly

    Thank You

  • Hank Freeman

    Thank you for the direct information.. It was what I needed

  • devil_D

    Thanks a lot!

  • John C

    Worked a treat – thanks.

  • Guy

    Great, thanks

  • juan

    This is exactly what I needed…Thank you for your post.

  • Chris

    This just saved me a ton of headache. Thank you!

  • Anonymous

    For existing applications accessing my SQL DB, If i perform moving tempdb to other drive do I need to reinstall them again.

  • Sherri

    This was wonderful – Thank you so much!

  • Gammon4422

    Great article, very concise and to the point!

  • Tariq alalhabash

    perfect

    thanks alot for this hint, it helped me alot and solved my problem

  • Hamelind

    Perfect !!! Thanks a lot

  • Abc

    don’t forget to delete the old tempdb files as they are not removed automatically.

  • Cdl

    Thank you, worked perfectly

  • morbius

    awesome. much better than microsoft method of adding parameters to the startup in configuration manager to you can detach and attach db. thank you!

  • rale

    amen, brother.

    big thanks!

  • Ganesh Jagdale

    thank you & well come