PRB: 'Temp_db' Device Causes Problems Starting SQL Server

This article was previously published under Q158586
This article has been archived. It is offered "as is" and will no longer be updated.
If SQL Server is started so that tempdb is placed in RAM (either byexplicitly setting the 'tempdb in RAM' option or by starting in a minimalconfiguration mode using the -f parameter at the command line), SQL Serverfails to start properly if a device with a device name of 'temp_db'already exists.
When tempdb is placed in RAM, SQL Server first attempts to create a RAMdevice with a logical name of 'temp_db,' and then to create the tempdbdatabase on this device. If a device already exists with the name'temp_db,' the attempt to insert an entry into the sysdevices table willfail; thus tempdb cannot be created.

The SQL Server errorlog will contain the following sequence of errormessages:
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 likelyreport other error messages as well.

If you attempt to use tempdb during startup (for example, in a startupstored procedure), the following error messages may be reported and theserver 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 abovemessages will be logged, but the server will not stop.

In order to start the server, go to a command prompt and navigate to theBINN directory where SQL Server was installed (for example, SQL60\BINN forSQL Server 6.0, or MSSQL\BINN for SQL Server 6.5). Then use the followingcommand 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. Makesure that you stop any automated processes that may be trying to log in toSQL Server (e.g., SQL Executive, Performance Monitor, etc.).

Once SQL Server is started, use ISQL/w to connect to SQL Server. Note thatyou will not be able to use the SQL Enterprise Manager; it is notavailable with these startup parameters (because it uses tempdb). Onceconnected, 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 willneed to be dropped, using the DROP DATABASE command, so that the temp_dbdevice can be deleted.

If SQL Server still fails to successfully start after following the abovesteps, and tempdb had been explicitly placed in RAM, use the followingcommand 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 defaultdatabase 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 andcreate a device with a different name and then expand tempdb onto this newdevice.
sql60 sql65 minimal configuration

Article ID: 158586 - Last Review: 10/26/2013 02:48:00 - Revision: 4.0

Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition

  • kbnosurvey kbarchive KB158586