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.

17 Responses to “SQL Server 2005: Move tempdb”

  1. November 25, 2008 at 11:14 am, sir said:

    Thanks

    Reply

  2. February 26, 2009 at 1:20 am, Kelly said:

    Thank You

    Reply

  3. March 03, 2009 at 5:50 pm, Hank Freeman said:

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

    Reply

  4. March 12, 2009 at 12:41 pm, devil_D said:

    Thanks a lot!

    Reply

  5. March 12, 2009 at 4:01 pm, John C said:

    Worked a treat – thanks.

    Reply

  6. March 19, 2009 at 5:12 pm, Guy said:

    Great, thanks

    Reply

  7. June 19, 2009 at 2:35 am, juan said:

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

    Reply

  8. September 04, 2009 at 1:27 pm, Chris said:

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

    Reply

  9. February 14, 2010 at 12:06 pm, Anonymous said:

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

    Reply

  10. April 13, 2010 at 8:59 pm, Sherri said:

    This was wonderful – Thank you so much!

    Reply

  11. September 07, 2010 at 5:37 pm, Gammon4422 said:

    Great article, very concise and to the point!

    Reply

  12. September 29, 2010 at 2:41 pm, Tariq alalhabash said:

    perfect

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

    Reply

  13. November 05, 2010 at 8:56 am, Hamelind said:

    Perfect !!! Thanks a lot

    Reply

  14. November 25, 2010 at 12:10 pm, Abc said:

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

    Reply

  15. March 15, 2011 at 12:35 pm, Cdl said:

    Thank you, worked perfectly

    Reply

  16. June 29, 2011 at 2:15 pm, morbius said:

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

    Reply

  17. December 13, 2011 at 10:04 pm, rale said:

    amen, brother.

    big thanks!

    Reply

  18. October 23, 2012 at 7:50 am, Ganesh Jagdale said:

    thank you & well come

    Reply

Leave a Reply