Moving the tempdb database

By Bill Graziano on 5 November 2007 | 5 Comments | Tags: Administration


Moving the tempdb database in SQL Server is a simple process but does require the service to be restarted.

By default, the tempdb database is created in the same location as master, model and msdb.  tempdb is used to store temporary user created objects (e.g. temp tables), temporary internal objects (e.g. work tables for sorting) and any row version data.  Each time SQL Server is started all the objects in tempdb are deleted but the file sizes are retained.  People primarily want to move tempdb to increase performance or deal with file size issues.

The first step is to determine the logical file names of of the data files for tempdb.  You can run the following command in SQL Server 2000 or SQL Server 2005:

USE tempdb
GO
EXEC sp_helpfile
GO


name      fileid filename                      
--------- ------ -------------------------------
tempdev        1 c:\Data\MSSQL\data\tempdb.mdf 
templog        2 c:\Data\MSSQL\data\templog.ldf

I've omitted some extra columns from the result set.  The logical names always seem to be tempdev and templog.  Knowing this we can run the following ALTER DATABASE statement in SQL Server 2000 or SQL Server 2005 to move tempdb.  Set the FILENAME parameter to the location where you'd like each file.

USE master
GO
ALTER DATABASE tempdb 
	MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb2005.mdf')
GO
ALTER DATABASE tempdb 
	MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb2005.ldf')
GO

SQL Server will return a message indicating that the old files can be deleted after restarting SQL Server or that the new files will be available after the database is restarted depending on which version of SQL Server you're using.  In either case, you'll need to restart SQL Server for the change to take effect.

Discuss this article: 5 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Advanced SQL Server 2008 Extended Events with Examples (25 May 2009)

Introduction to SQL Server 2008 Extended Events (19 May 2009)

Monitoring SQL Server Agent with Powershell (24 March 2009)

SQL Server Version (29 January 2009)

Scheduling Jobs in SQL Server Express - Part 2 (1 December 2008)

Alerts for when Login Failures Strike (14 July 2008)

Using xp_ReadErrorLog in SQL Server 2005 (12 May 2008)

Centralized Asynchronous Auditing across Instances and Servers with Service Broker (20 August 2007)

Other Recent Forum Posts

SQL Server 2008 on windows 8 (3 Replies)

Encryption and decryption of column values (2 Replies)

Multiple criteria Rating (3 Replies)

Calculate lengths (7 Replies)

insert values of a column from one table to column (10 Replies)

Update values in two tables via stored procedure (4 Replies)

Update rows with increment date (19 Replies)

sqlit, transaction (1 Reply)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -