FIX: Taking tempdb Out of RAM Can Cause Error 1808

• Microsoft SQL Server, version 4.2

SYMPTOMS

Taking tempdb out of RAM when no default device exists causes attempts to start SQL Server to fail with an error 1808.

When tempdb is taken out of RAM, page 91 of the SQL Server for Windows NT "Configuration Guide" says a 2-MB tempdb is placed on the master device. SQL Server actually places the 2-MB tempdb onto a default device, so page 91 is true only if the master device is also a default device.

After installation, master is the only default device. However, you can change this by using sp_diskdefault. If the sa issues:

   sp_diskdefault master,defaultoff
   go
   sp_diskdefault other_dev,defaulton
   go

If the sa removes all default devices in a system where tempdb is in RAM, and then issues:

   sp_configure 'tempdb in ram',0
   go
   reconfigure
   go

   Crdb_disk : default disk not found. Cannot complete
   create/alter database command
   Problem creating Temporary Database - if out of space,
   please extend and reboot. If some other problem, please
   contact Technical Support. Failed to create temp db

WORKAROUND

Restart SQL Server in recover master only mode and add a default device. You should backup your system up prior to attempting this procedure.

Restart SQL Server from the command line as follows:

   sqlservr -dc:\sql\data\master.dat -ec:\sql\log\fix1808.log -T3608

   sp_diskdefault master, defaulton
   go
   shutdown
   go

   disk init
   name='tempdev',physname='c:\sql\data\tempdev.dat',
             vdevno=X,size=1024
   go
   sp_diskdefault tempdev, defaulton
   go
   shutdown
   go

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.2. This problem was corrected in SQL Server version 6.0. For more information, contact your primary support provider.