Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Moving TempDB

By Andy Warren,

Recently I had a question about moving TempDB, it didn't behave as the user had anticipated. TempDB is a special case, so let's look at how to move it and what happens when the service is restarted. Our first thought on moving a database is to detach, cut/paste, reattach. Works well enough for normal databases but not the system ones (system=we need them all the time). Plan B is to do a backup, then a restore with move. Again, that works for normal databases, but that doesn't solve our problem.

So finally we look it up, here's the code needed to move TempDB to C:\:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'c:\templog.ldf')
 

If you think about what that does internally, we're really modifying sys.database_files. Run the query select * from tempdb.sys.database_files to see the files for any database. For my current tempdb, it returns:

If I run the ALTER from above, I get this message:

And if I re-run the select, I get this:

What's unusual about that is that it shows the files being in the new location, but if you look on disk there are no files in the new location, they are still at the old location! Everything works. So how do the files get to the new location, and when? If you execute the script and then stop the service, you'll see that TempDB still exists in it's old location and does not exist at the new location. When the service is restarted TempDB will be created in it's new location, but the old TempDB files will still exist at the original location. The old TempDB files can be deleted at any time once the service has been stopped.

I went a step further and stopped the service again, renamed the two TempDB files (just in case!) and restarted the service without error. So we can see that we get a new TempDB if we execute a move, or delete a file, but what happens on just a plain service restart? I executed the following query in TempDB:

create table AndyTest (SomeKey int)

After restarting the service, did the table still exist? No, I get an invalid object name error. The only way to add objects to TempDB that will persist across a service restart is to add the objects to Model, or to execute a startup script that will recreate them (I like the latter approach better if you have to do this).

Moving TempDB isn't something we'll do often, but at least it's about as painless as you can ask for once you know the secret syntax.

Visit my blog at http://blogs.sqlservercentral.com/andy_warren/default.aspx

Total article views: 5944 | Views in the last 30 days: 6
 
Related Articles
BLOG

How to change TempDB system Database files location?

Steps to change TempDB system Database files location :- 1) Check current file location of TempDB ...

FORUM

How to change Default Database locations without a restart

How to change Default Database locations without a restart

FORUM

Restart SQL Server services

Restart SQL Server services

ARTICLE

Has tempdb grown since the last restart?

Check tempdb to see if it has been autogrown since the last restart. If it has, there may be an oppo...

SCRIPT

PowerShell - Restart Services

A PowerShell script which restarts any service running on a server.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones