SQL Server 2005: Move tempdb

Home -> Database

10790 views

From the computer of: shamanstears (738 recipes)
Created: May 08, 2007     Updated: May 09, 2007


Add a comment

Add to:
Add to stumbleuponAdd to del.icio.usDigg itAdd to FURL

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.

Subscribe to the Tech-Recipes Newsletter

You can get tips like this delivered in your email every week!

Enter your Email

We will never, ever sell your email address or spam you.





Related recipes:

  SQL Server 2005: The Copy Database Wizard
  SQL Server 2005: How To Enable xp_cmdshell
  SQL Server 2005: What Service Pack is Installed?
  SQL Server 2005: Include Column Headers When Copying Grid Query Results
  SQL Server 2005: How to Attach (or Reattach) a Database Missing the LDF
  SQL Server 2005: How To Set Up Database Mail
  SQL Server 2005: Return a Comma Delimited Result Set from Queries
  SQL Server 2005: How To Perform a Full Backup of a Database
  SQL Server 2005: Enforce Password Policy and Expiration
  SQL Server 2005: Configure Max Number of Characters Displayed in Each Column

 

Sponsored links

 

Affiliated Sites


Certification Journals
Free certification exam study guides and help

Tech-Recipes Blogs
More tips and news from the tech-recipes authors

 

Login

Nickname

Password

Don't have an account yet? You can create one. As a registered user you have some advantages like theme manager, comments configuration and post comments with your name.