Move TempDB Sql Server 2005
I recently needed to move the TempDB on my Sql Server so I looked in Books Online and my initial thought based on what it said was that there is no way that will work. Basically it says to find where the files are, and then move them to the new location followed by an alter statement to point the database at the new location. Well of course this didn't work because the tempdb files are in use. After thinking about it I just decided to try to alter the master database and point it at where I wanted the tempdb files to be and then just restart since Sql Server creates the tempdb again when you restart it. Sure enough it worked. Here are the steps I took.
1. Find out where the TempDB files are (not that you really need to know for anything since you can't move them without stopping SQL Server).
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
2. Alter the master database and point the tempdb to the new location.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SqlServer\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\SqlServer\Data\templog.ldf');
GO
3. Restart SQL Server
Upon restarting you will see new TempDB files created. Alternatively I suppose you could do steps, 1 and 2 above and then stop SQL Server, move the TempDB files to the new location, and then start SQL Server. I suppose that would be slightly better since then you wouldn't have to wait for the TempDB files to auto-grow which can be a performance hit. Either way if you try to follow books online you won't have any luck.