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.
« ‹ | December 2024 | › » | ||||
Sun | Mon | Tue | Wed | Thu | Fri | Sat |
1 | 2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 10 | 11 | 12 | 13 | 14 |
15 | 16 | 17 | 18 | 19 | 20 | 21 |
22 | 23 | 24 | 25 | 26 | 27 | 28 |
29 | 30 | 31 |