SQL - The TempDB

One of the problems with inheriting an SQL install is that you have to spend a little time documenting the current setup (unless you have replaced a document-happy sysadmin.)

One of the issues I came accross recently, involved a small system (C:) drive and an over-zealous app that used the tempDB heavily. The tempDB was setup on the C: drive (although data and translogs were on alternate drives.)

I found the following useful for moving the tempDB files;

USE master
  GO
  ALTER DATABASE tempdb modify file (name = tempdev, filename = 'E:\SQLServerDataFolder\tempdb.mdf')
  GO
  ALTER DATABASE tempdb modify file (name = templog, filename = 'E:\SQLServerDataFolder\templog.mdf')
Restart SQL Server for the changes to take effect. One it's restarted you can delete the tempdb.mdf and templog.mdf from the old location.

Thanks to Paul Mrozowski's Blog for this one.

Also of interest may be this MS KB; How to shrink the tempdb database in SQL Server