Moving TempDB

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:

Zoom out   |   Open in new window

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

Zoom out   |   Open in new window

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

Zoom out   |   Open in new window

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