This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing necessary steps to move SQL Server TempDB from one location to another location.
When you are searching for TempDB optimization tips, you can find one tip like to change the location of TempDB because it should not be with SQL Server data file and the main reason is unpredictable size and maintenance of it.
I would suggest following steps to move your TempDB from one drive to another drive:
Identify the location of TempDB:
|
1 2 3 4 5 6 |
SELECT name ,physical_name AS Location FROM sys.master_files WHERE database_id = DB_ID(N'tempdb') GO |
Change the location of TempDB, using ALTER DATABASE:
|
1 2 3 4 5 6 7 |
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\YourDrive\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\YourDrive\templog.ldf'); GO |
Once you execute above ALTERs, you will get below messages:
|
1 2 |
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started. |
Next, Restart the Service of SQL Server.
Leave a Reply