It’s a solid best practice to store temp db files on a separate drive, away from your C: drive. This is because temp db can grow very large, very quickly, sometimes within minutes depending on workloads. This growth affects both the temp db data (.ndf) and log (.ldf) files.
This post is a quick tutorial on how to move all temp db files from one location to another in SQL Server. Keep in mind that a planned maintenance window is required when performing this on production servers.
Demo: Moving Temp DB Database Files
1. Use the sp_helpfile system stored proc to view the logical names and file paths of your database files.
2. Use the SQL ALTER DATABASE statement to change the file paths for each temp db file. Update the name and full file path inside the single quotes as needed:
If you need to generate these statements quickly for multiple files, this should help you out:
3. After running the ALTER statements, you’ll notice the output message states that the changes will take effect the next time the database is started. Restart the SQL Server service to apply the changes.
4. After restarting, verify that the files have been moved to the new location by running sp_helpfile again:
5. We can open the Shrink File window in SQL Server Management Studio (SSMS) to visually confirm the new file locations:
6. DBA’s will often send a test email after such changes to ensure this server functionality is running smoothly:
7. Finally, remember to delete the old temp db files from their original location. If the files are on the same disk, this cleanup is especially important to free up space.
Note: You won’t be able to delete temp db files that are currently being used by an active SQL Server service. Ensure the files are no longer in use before attempting removal. We can also look at the last date modified on the files to verify activity.
Moving temp db files to a dedicated drive is a recommended best practice for optimizing SQL Server performance and ensuring stability under heavy workloads. Follow the steps above carefully, and always double-check the file locations and configurations after making changes.
Feel free to add a comment below if you have any questions or issues on this.
Leave a Reply