New, faster disks arrived? Maybe you''ve done a reshuffle and ended up with a clean, unused physical drive? Either way your tempDB needs to be relocated. Doing so is actually incredibly simple and requires just a few short steps.
Let’s presume for a moment that my tempDB is in completely the wrong location and I need it moved…
As you can see, it’s clearly not where it belongs, so I’m going to move it from here and into the tempDB folder.
Firstly, you may not actually know for certain where your tempDB is hiding. There are a couple of ways you can find this out, you can use the GUI by right clicking tempDB and selecting Properties:
Then click on the Files tab on the left:
Next, scroll across until you see the Path shown:
The two other ways are both faster and involve a little T-SQL. You can use either of the following:
All of these should lead you to the current location of your tempDB files.
Knowing the location is not actually essential for this process, but it’s always good to know.
To actually move the location of tempDB (in my case to “D:\Microsoft SQL Server\tempDB\”) you simply need to run the following:
You can see the confirmation message specifies that this will take effect the next time you restart SQL Server. This is because at restart SQL will drop and re-create tempDB and therefore it will do so at this new location.
Quick service restart and there we have it…