How To Move The tempDB Database
Published: Mar 03, 2014
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:

or

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…

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron