Backtrack:  
 
by lunarg on May 26th 2015, at 14:17

You can move the tempdb of a SQL Server instance to another location using T-SQL, but this requires a little bit of downtime: setting a new location will only take effect after a restart of the SQL Server instance.

First, retrieve the tempdb's current location and logical name:

Use tempdb
GO
SELECT name,filename FROM sys.sysfiles
GO

This should give you two entries: one for the actual database, and one for the transaction log of tempdb. Now that we have the location and logical name, we can change it:

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\newdir\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'T:\newdir\templog.ldf');
GO

Replace the path of the second argument of each ALTER DATABASE with the location and/or filename.

After changing this, restart the SQL Server instance on which you performed the relocation.

After the restart, you can verify the new location by running the first query again. Its filename(s) should now display the new location for tempdb.