16 January 2009

How To: Change the tempdb data files location

To change the tempdb data files location:
1) Determine the logical file names for the tempdb database.
The logical name for each file is contained in the NAME column.

USE tempdb
EXEC sp_helpfile

2)Change the location of each file using ALTER DATABASE.
USE master
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\DBData\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog,FILENAME = 'E:\DBData\templog.ldf')
3) Stop and restart SQL Server.

1 comment:

Nicholas Davis said...

This worked for me. A word of advice, though, make sure the directory you are moving your tempdb to has proper security in place for SQL to access it. I had been receiving an "Unhandled Exception" until I added the SQL user to the security.