Move TempDB

A short script showing how to move the tempdb data and log files to a new location.

Published Apr 1, 2006

Moving tempdb to faster storage is one of the highest-impact changes you can make. This short script shows the sequence: check the current file layout with sp_helpfile, then issue ALTER DATABASE ... MODIFY FILE for each tempdb file pointing at its new location. The changes take effect on the next restart.

USE tempdb
GO
EXEC sp_helpfile
GO

USE master
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\datatempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\datatemplog.ldf')
GO