Microsoft SQL 2005 – Moving the TEMPDB database

by Otto on January 28, 2008

The data warehouse application I built is growing fast!  Some of my queries create temporary tables, which means my TEMPDB database grows with those queries.  So much that I needed to move my TEMPDB database from the primary partition to a much larger partition.

Microsoft has a great knowledge base article, How to move SQL Server databases to a new location… which I used to move my TEMPDB database.  If you read the article, you might think you need to re-configure SQL Server with the -c -m -T3608 startup switches, but that isn’t necessary if you just need to move the TEMPDB database.

Here are the exact steps I used:

1.  Stop all SQL Services
2.  Copy the TEMPDB database & log files from the current location
3.  Paste the TEMPDB database & log files to the destination location
4.  Restart all SQL Services
5.  Open Microsoft SQL Server Management Studio
6.  Navigate to the MASTER database and click the New Query button
7.  Run the following query:

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf')
go

Modify the "filename = ‘E:\" part with the directory you pasted the copy of the database and log files.  And execute the query.

8.  Stop and Start all SQL Services
9.  Check the TEMPDB database file properties to ensure the new directory is being used

Hope this helps someone else out there.

Comments on this entry are closed.

Previous post:

Next post: