Overview
Several of the instances in an Availability Group (AG) that I look after, have some of the Temp DB data files stored on the C: drive. This is generally not a good idea – certainly when you have another drive allocated for the TempDB. All TempDB files need to be in the correct location and moving them isn’t as difficult as some people would appear to believe.
Background
I need to move the TempDB files, so that they are all on their allocated drive and not shared between that correct drive and the C: drive.
Initially I read several articles that explained the issues with messing around with TempDB and the variety of painful situations that you can get into, even if it all runs smoothly.
It was an article on Brent Ozar’s site that made me realise that I was barking up the wrong tree, and moving a TempDB around is actually quite simple.
Remember – the TempDB is destroyed when the SQL instance is shut down, and recreated when the instance starts. So you don’t actually need to move any existing files – just tell it where you want the files to be, the next time the instance starts.
SQL will return an error if the new targets don’t exist and will not change the system tables.
Test Environment
I have a three-node AG, based upon a previous article. However, when I installed the SQL Server software, I deliberately installed the TempDB across two different drives.
Figure 1: The TempDB Allocations
I would like to move all TempDB data files to reside in ‘D:\TempDB’.
Changing the Files
Using the script from Brent’s article, I have switched SSMS to ‘Results to Text (Ctrl+T)’, just to make it a little more obvious and I’ll copy/paste the results to a new query window before editing it.
Figure 2: Running the Initial Query
I will now copy thr results into a new query window and edit the file locations to what I require.
Note that Brent’s query uses the logical files names as the basis for the physical file names. This means that the filenames will change. Not a problem for this case but you might want to change those too.
I’ve changed the file locations to those that I require. When I execute the script it responds that the system catalog has been updated and these changes will take effect when the instance is restarted. Right now, TempDB still has the files on the C: and D: drives – even though the properties view of TempDB will now show the new file details.
Figure 3: The New File Locations Specified
Restarting the Instance
Within the AG properties, I’ve ensured that this node is asynchronous, with manual failover – because I don’t want to upset the AG any more than I need to. If I had a large number of highly active databases on this instance I might also suspend data movement for those databases that are part of the AG, just to reduce the potential for the AG to get annoyed.
Having restarted the instance, it has restarted without incident and the files are now where I wanted them to be.
Figure 4: The updated properties for TempDB
Conclusion
TempDB is one of the easiest system databases to relocate. Check the results of your query carefully, to ensure it isn’t raising any errors, because SQL will not process the instructions if the folder doesn’t exist. Of course, if you have specified the wrong folder and it does exist, SQL will put the TempDB where you have instructed it to – not necessarily where you wanted it.
Notes on SQL 