Microsoft KB Archive/108664

{|
 * width="100%"|

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