Search
  Home
  Articles
  Backup
  Books
  Certification
  FAQ
  Products
  Replication
  Scripts
  Seminars
  Training
  TSQL
MSDN Fourms
Philippine SSUG
  Resume
MHS Enterprises
FilAm Software
AcrylicAcetate.com
Bargain Humidors
Western Humidor



How do I remove the tempdb database from master/default devices?

Do the following for SQL 6.5 and below
  1. Configure tempdb to be in RAM for 2 MB. Use SQL EM or sp_configure.
  2. Stop and restart SQL Server.
  3. Add a new device for tempdb. Do not call it temp_db - any other name should be ok
  4. Make that new device a 'default' device; make sure no other device is marked as default, especially master. You can check/change default status either using SQL EM, or the sp_diskdefault stored-procedure. Both are fully described in the books-online.
  5. Configure tempdb to NOT be in RAM (set value to 0)
  6. Stop and restart SQL Server
  7. Re-mark whichever device(s) you want to be default.

For SQL 7.0 :-

  1. alter database tempdb alter file <logicalname> filename = <newlocation> ....
  2. Stop and restart Sql Server

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.