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.

 
 
« April 2024»
SunMonTueWedThuFriSat
 123456
78910111213
14151617181920
21222324252627
282930    
 
Links
 
Quote
« When a bird does poo poo in your eye, be happy elephants don't fly. »