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.

 
 
« March 2025»
SunMonTueWedThuFriSat
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
 
Links
 
Quote
« Have you tried turning it off and on again? »
The IT Crowd