Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.