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