In a recent interview, a candidate was asked if TempDB can be moved to a different drive. The candidate was surprised by the question, so I decided to write this blog post to explain the process. Moving TempDB to another drive can be beneficial for two main reasons:
- When TempDB grows too large and the existing drive does not have enough space.
- Moving TempDB to a different physical drive can improve database disk read performance, as the files can be read simultaneously.
Follow the steps below to move the TempDB database and log files from one drive (C:) to another drive (D:) and (E):
- Open Query Analyzer and connect to your SQL Server.
- Run the following script to get the names of the files used for TempDB:
USE TempDB
GO
EXEC sp_helpfile
GO
The result of the above script will display the names of the files used by TempDB, such as “tempdev” and “templog”. These names will be used in the next step.
- Run the following code to move the MDF and LDF files:
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'D:\data\tempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'E:\data\templog.ldf')
GO
The above code modifies the definition of the TempDB database to point to the new file locations. However, no changes are made to TempDB until SQL Server is restarted.
Please stop and restart SQL Server to allow it to create the TempDB files in the new locations.
By following these steps, you can successfully move the TempDB database and log files to another drive in SQL Server.