SQL Server 2005: Move tempdb

Contributed by Rob Rogers  
Tagged: Database  

• Tweet

Browse to next tech-recipes article

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.

You may also like -

SQL Server 2008/2008 R2: Move tempdb to Improve PerformanceSQL Server 2005: What Service Pack is Installed?SQL Server 2005: How To Enable xp_cmdshellSQL Server 2005: How To Set Up Database MailSQL Server 2005: The Copy Database WizardSQL Server 2005: Include Column Headers When Copying Grid Query ResultsInstall a Windows 2003 DNS serverSQL Server 2005: Return a Comma Delimited Result Set from Queries

 

17 Comments -

1. sir said on November 25, 2008

   Thanks

2. Kelly said on February 26, 2009

   Thank You

3. Hank Freeman said on March 3, 2009

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

4. devil_D said on March 12, 2009

   Thanks a lot!

5. John C said on March 12, 2009

   Worked a treat – thanks.

6. Guy said on March 19, 2009

   Great, thanks

7. juan said on June 19, 2009

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

8. Chris said on September 4, 2009

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

9. Anonymous said on February 14, 2010

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

10. Sherri said on April 13, 2010

    This was wonderful – Thank you so much!

11. Gammon4422 said on September 7, 2010

    Great article, very concise and to the point!

12. Tariq alalhabash said on September 29, 2010

    perfect

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

13. Hamelind said on November 5, 2010

    Perfect !!! Thanks a lot

14. Abc said on November 25, 2010

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

15. Cdl said on March 15, 2011

    Thank you, worked perfectly

16. morbius said on June 29, 2011

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

17. rale said on December 13, 2011

    amen, brother.

    big thanks!

RSS feed for comments on this post. TrackBack URL

Leave a comment -