Microsoft KB Archive/108664

FIX: Taking tempdb Out of RAM Can Cause Error 1808

Q108664

The information in this article applies to:

Microsoft SQL Server version 4.2x

BUG# NT: 583 (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

then the master database is no longer a default device. If the sa takes tempdb out of RAM, the 2-MB tempdb is not be placed on the master device, but is placed on other_dev, provided other_dev has 2 MB of free space available.

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

Any subsequent attempt to start SQL Server fails with error 1808:

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

After SQL Server initializes, either make a device known to contain at least 2 MB of free space on a default device:

   sp_diskdefault master, defaulton
   go
   shutdown
   go

Or, add a new device with at least 2 MB and make it a default device:

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

where X is a known free device number. After SQL Server shuts down from either of these methods, it should be able to restart normally.

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.

Additional query words: sql6 sp_configure tempdb Windows NT

Keywords : kbother
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2