In this article, we will show you, How to move TempDB to a new drive or location in SQL Server with an example.
TempDB is a very important system database in SQL Server. It holds the global and temporary user data. Whenever we create any table with a temporary keyword, that also table will store inside the TempDB only. So, it is highly recommended to place the TempDB on separate disk drives from other user databases for performance reasons. Because if multiple users are connected to the SQL Server instance then each user session may generate its own temp table which can also lead to excessive I/O operations on a single disk drive and severely degrade the performance of the SQL Server instance. If a heavily used user database is placed on the same drive where tempdb resides, then also it will further degrade the performance of the SQL Server instance. So, we should always place the tempdb on a separate disk drive from other user databases for optimal performance of SQL Server instance. Check RemoteDBA.
There are multiple ways to move TempDB to a new location or disk drives in SQL Server such as:
- Using the SSMS GUI option
- By using T-SQL Script
- Let us see each option one by one in detail with examples.
- Move TempDB to New Location or Drive Using SSMS
This is the recommended and easier method to move TembDB to a new location. Follow the below steps to do so:
Step 1: Right-click on the server node and select Properties. It will open the Server Properties wizard window like the below screenshot.
Step 2: In the Server Properties window, click on Database Settings from the left side pane and you can also see the current location of TempDB database files as shown below.
Step 3: Now click on Add button to add a new file for tempdb database. It will open Add Database File dialog box like the below screenshot.
Step 4: In the Add Database File dialog box, specify the new location for tempdb files and click on OK to continue.
In our example, we are moving TempDB files from the old location E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA to new location F:\SQLData\. You can choose any other desired location as per your environment and requirements.
Step 5: Once you click on the OK button in the previous step, it will open below Confirm File Relocation dialog box. Here you can see the old and new locations for TempDB files. Click on OK to continue.
Step 6: Now click on the OK button in the Server Properties window to close it.
From the above steps, we can see that it is very easy to move TempDB database files to a new location by using the SSMS GUI option.
Move TempDB to New Location or Drive Using T-SQL Script
This is another method to move tempdb database files from one location or disk drive to another. In this method we will use ALTER DATABASE command as shown below:
Check also – June wedding benefits
USE master;
GO
— To view the current location of tempdb files
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO
— To Change the location of tempdb data file (‘tempdev’) to new location
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘D:\SQLData\tempdb.mdf’);
GO
— To Change the location of tempdb log file (‘templog’) to new location
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘L:\SQLLog\templog.ldf’);
GO
In the above script, we are moving the tempdb data and log files from the old location to the new location D:\SQLData\ and L:\SQLLog\. You can choose any other desired location as per your environment and requirements.
Conclusion:
That’s it! These are the two methods that you can use to move TempDB to a new location or disk drive in SQL Server. Moving TempDB to a separate disk drive is always recommended for performance reasons. I hope you enjoyed this article. Please post your feedback, questions, or comments below.
