A common best practice in SQL Server, is to have your Temp TB files stored on a separate drive, especially ensuring it’s not on the C:\ drive, sharing with the Windows OS. This is because the SQL Server Temp database can grow very large, very quickly.
The growth of the Temp DB files depend on the workload and queries on the SQL Server. Temp DB will need to grow and will be utilized depending on the type of SQL query being executed on the SQL Server.
This post is a tutorial on how to move all Temp DB files from one volume to another in SQL Server. Downtime is required for this action, so we should ensure we have a planned maintenance window for this work.
Move Temp DB to Another Drive
1. View Logical Temp DB File Names
To move a SQL Server Temp DB to another drive, we should begin by viewing logical file names of your Temp DB. Running the T-SQL below shows us the current configuration of Temp DB.
-- check temp db file locations USE temp db; GO exec sp_helpfile;
2. Move Temp DB Files to Another Drive
To amend the file location of Temp DB files in SQL Server, run the ALTER SQL statement below. You’ll need to change the FILENAME parameter to your new temp db file location.
Copy the output SQL from the above command and run the ALTER DATABASE commands:
-- move temp_DB database to new location on disk -- (amend temp_db to actual db name) ALTER DATABASE temp_db MODIFY FILE (NAME = [tempdev], FILENAME = 'D:\mssql_temp_db\tempdev.mdf'); ALTER DATABASE temp_db MODIFY FILE (NAME = [templog], FILENAME = 'D:\mssql_temp_db\templog.ldf'); ALTER DATABASE temp_db MODIFY FILE (NAME = [temp2], FILENAME = 'D:\mssql_temp_db\temp2.mdf'); ALTER DATABASE temp_db MODIFY FILE (NAME = [temp3], FILENAME = 'D:\mssql_temp_db\temp3.mdf'); ALTER DATABASE temp_db MODIFY FILE (NAME = [temp4], FILENAME = 'D:\mssql_temp_db\temp4.mdf'); ALTER DATABASE temp_db MODIFY FILE (NAME = [temp5], FILENAME = 'D:\mssql_temp_db\temp5.mdf'); ALTER DATABASE temp_db MODIFY FILE (NAME = [temp6], FILENAME = 'D:\mssql_temp_db\temp6.mdf'); ALTER DATABASE temp_db MODIFY FILE (NAME = [temp7], FILENAME = 'D:\mssql_temp_db\temp7.mdf'); ALTER DATABASE temp_db MODIFY FILE (NAME = [temp8], FILENAME = 'D:\mssql_temp_db\temp8.mdf');
SQL Output: The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.
The above output message informs that we have to restart the SQL Service for this change to take effect. So let’s do that now.
3. Restart SQL Server Service
For the Temp DB move changes to take affect we must restart the SQL Server service. There are a variety ways of restarting the SQL Server service; in this demo we will restart via SQL Server Configuration Manager:
4. Check Temp DB File Locations
When SQL Server is back online, we can should view the new temp db file location to verify its safely running in its new directory. More importantly, we should remember to delete the old Temp DB files from where you moved them from.
I hope this guide provided you with some insight and that your change goes without issues! Feel free to check out my Database Files Blog Tag for more posts on managing SQL Server database files.
Leave a Reply