Moving TempDB Data and Log Files To A New Drive On Your SQL Server

Though it’s not something that you should have to do often, occasionally you may find that you need to move your tempdb to a new drive.  This scenario occurred recently when, in the process of building out a virtual server, only 2 additional drives were created for me. Since it is best practice to put your data, log and tempdb on separate drives, I was left with having to temporarily put the tempdb on the E:\ drive when initially configuring the new installation of SQL Server.

Eventually the new drive, we’ll call it R:\, was added to the server and I now needed to move tempdb to this drive.

In this case I already knew where my tempdb was located but this may not always be the case.  The first step you should take when moving tempdb to a new drive is to verify it’s current location.  The following script can be used to do just that.

This script will return the name, physical location and current status of the tempdb data and log files.  The results should look something like this:


Now that I have confirmed the location of the tempdb data and log files my next step will be to move them to the new location.  Before moving them I’ll want to make sure I have created any necessary folders on the new drive.  In the example below I am putting them in the R:\SQL folder so I would need to go create a folder named SQL on the R:\ drive.

The script below can be used to change the location of both the tempdb data and tempdb log files.  You can modify the FILENAME section to correspond to the location of your choosing.

The final step is to restart the instance of SQL Server.  You can do this by either right-clicking on the SQL Server name in the Object Explorer and selecting to Stop, then Start the service or by opening up SQL Server Configuration Manager and doing it from there.  I prefer using SQL Server Configuration Manager for this as I’ve had issues in dealing with services when remotely connected to SQL server through Management Studio.  Though rare, it’s happened enough to encourage me to always log directly into the server to start/stop services.

Once the service is back up and running, you can re-run the first script to confirm the new location of your TempDB data and log files.

The above steps can also be used to move the msdb and model system databases.  You’ll just need to modify the ALTER DATABASE, NAME and FILENAME values to point to the applicable database.   If you’re wanting to move the master database it requires a slightly different approach.  To learn more about this you can reference my post on Moving The master System Database To A New Location In SQL Server.