Moving tempdb
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.
The primary reasons for wanting to move the tempdb are to increase performance or deal with file size issues.
Step 1: Determine\confirm the logical file names of of the data files for tempdb. You can run the following command in SQL Server 2000 \2005\2008:
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
Unless modified the default will be 1 data file and 1 log file with the standard names tempdev and templog.
Step2: ALTER DATABASE Setting the FILENAME parameter to the location where you’d like each file.
USE master GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘D:\tempdb\Data\tempdb.mdf’)
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘D:\tempdb\Log\tempdb.ldf’)
GO
Step 3: Restart SQL Server (Services)
Step 4: Delete old tempdb files
Volla you are done.
