Moving the tempdb database
By Bill Graziano
on 05 November 2007
| 3 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.
