PRB: 'Temp_db' Device Causes Problems Starting SQL Server

ID: Q158586

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

SYMPTOMS

If SQL Server is started so that tempdb is placed in RAM (either by explicitly setting the 'tempdb in RAM' option or by starting in a minimal configuration mode using the -f parameter at the command line), SQL Server fails to start properly if a device with a device name of 'temp_db' already exists.

CAUSE

When tempdb is placed in RAM, SQL Server first attempts to create a RAM device with a logical name of 'temp_db,' and then to create the tempdb database on this device. If a device already exists with the name 'temp_db,' the attempt to insert an entry into the sysdevices table will fail; thus tempdb cannot be created.

The SQL Server errorlog will contain the following sequence of error messages:

Attempt to insert duplicate key row in object 'sysdevices' with unique index 'sysdevices' (Msg 2601)

crdb_tempdb: Unable to move tempdb into RAM; RAM device doesn't exist, cannot be created, or doesn't have enough space for tempdb.

The server will continue through the startup process, and will likely report other error messages as well.

If you attempt to use tempdb during startup (for example, in a startup stored procedure), the following error messages may be reported and the server will fail to start:

Msg 906: Could not locate row in sysobjects for system catalog 'sysobjects' in database 'tempdb'....

Msg 615: Unable to find database table id = 2, name = 'tempdb'....

If you attempt to access tempdb after the server is started, the above messages will be logged, but the server will not stop.

WORKAROUND

In order to start the server, go to a command prompt and navigate to the BINN directory where SQL Server was installed (for example, SQL60\BINN for SQL Server 6.0, or MSSQL\BINN for SQL Server 6.5). Then use the following command line, which is case sensitive, to start SQL Server:

sqlservr -c -m -T3609 -T4022

Please note that the use of -m starts SQL Server in single user mode. After the first successful login, any subsequent requests will get a "Login failed" message until the first connection has logged out. Make sure that you stop any automated processes that may be trying to log in to SQL Server (e.g., SQL Executive, Performance Monitor, etc.).

Once SQL Server is started, use ISQL/w to connect to SQL Server. Note that you will not be able to use the SQL Enterprise Manager; it is not available with these startup parameters (because it uses tempdb). Once connected, use the following command to drop the existing device named 'temp_db' so that SQL Server can create that device upon startup.

sp_dropdevice 'temp_db'

If any other databases besides tempdb have been expanded onto the device, the command will report the names of these databases. These databases will need to be dropped, using the DROP DATABASE command, so that the temp_db device can be deleted.

If SQL Server still fails to successfully start after following the above steps, and tempdb had been explicitly placed in RAM, use the following command to try to place tempdb back on disk.

sp_configure 'tempdb in RAM', 0
reconfigure with override

This should place tempdb back on the default device with the default database size (typically 2 MB on master). Stop and restart SQL Server. At this point, you will want to drop the device with the name temp_db and create a device with a different name and then expand tempdb onto this new device.

Additional query words: sql60 sql65 minimal configuration

Keywords : SSrvAdmin SSrvISQL
Version : 6.0 6.5
Platform : WINDOWS
Issue type :