Presents your 
SQL SERVER E-NEWSLETTER for August 20, 2002

<------------------------------------------->

How can you move tempdb?

If tempdb is running out of disk space, you'll need to move it to
another disk. The user databases may require the space on which tempdb resides,
making it necessary to move tempdb onto another disk drive. Or,
performance may be degrading, and it may be time to move tempdb onto its own set
of disk drives or RAID set. Use the following command to retrieve the
location of tempdb database files:

EXEC sp_helpdb tempdb

To move the tempdb database, use the ALTER DATABASE command to change
the physical file location of the SQL Server logical file name associated
with tempdb. For example, to move tempdb and its associated log to the new
file locations f:\mssql7\DATA and g:\mssql7\DATA, use the following commands:

alter database tempdb modify file (name='tempdev',
  filename= 'f:\mssql7\DATA\tempnew_location.MDF')
alter database tempdb modify file (name='templog',
  filename= 'g:\mssql7\DATA\tempnew_loglocation.LDF')

The next time SQL Server is restarted, tempdb will be pointing to new
files, and the old files may be deleted.

----------------------------------------