MSSQLServer 2005: Moving TEMP Database

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.

First check where the current data and log file of tempdb is.

USE TempDB
GO
EXEC sp_helpfile
GO

-----------------------------------------------------------


Then run the below scripts

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:\tempdb.mdf') -- new location of tempdb data file
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'd:\templog.ldf') -- new location of tempdb log file
GO

----------------------------------------------------------
Stop the database instance

Start the Database and agent sequentially

then run this script again check whether the file destination is okay or not.

USE TempDB
GO
EXEC sp_helpfile
GO

Check whether changed or not.