How to recover SQL Server after a tempdb database is marked suspect (288809)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup
This article was previously published under Q288809

SYMPTOMS

When you try to start a SQL Server service, the service may fail because the tempdb database is marked suspect. You may see the following text in the SQL Server error log: 
2001-02-08 14:04:07.64 spid1    Clearing tempdb database.
2001-02-08 14:04:07.66 spid1    Creating file e:\MSSQL7\DATA\TEMPDB.MDF. 
2001-02-08 14:04:07.87 spid1    Closing file e:\MSSQL7\DATA\TEMPDB.MDF. 
2001-02-08 14:04:07.90 spid1    Creating file e:\MSSQL7\DATA\TEMPLOG.LDF. 
2001-02-08 14:04:07.97 spid1    Closing file e:\MSSQL7\DATA\TEMPLOG.LDF. 
2001-02-08 14:04:08.02 spid1    Opening file e:\MSSQL7\DATA\TEMPDB.MDF. 
2001-02-08 14:04:08.03 spid1    Opening file e:\MSSQL7\DATA\TEMPLOG.LDF. 
2001-02-08 14:04:08.32 spid1    Closing file e:\MSSQL7\DATA\TEMPDB.MDF. 
2001-02-08 14:04:08.34 spid1    Closing file e:\MSSQL7\DATA\TEMPLOG.LDF. 
2001-02-08 14:04:08.36 spid1    Starting up database 'tempdb'.
2001-02-08 14:04:08.36 spid1    Opening file e:\MSSQL7\DATA\TEMPDB.MDF. 
2001-02-08 14:04:08.39 spid1    Opening file e:\MSSQL7\DATA\TEMPLOG.LDF. 
2001-02-08 14:04:08.43 spid1    Bypassing recovery for database 'tempdb' because it is marked SUSPECT.
2001-02-08 14:04:08.47 spid1    Database 'tempdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
2001-02-08 14:04:08.50 spid1    WARNING: problem activating all tempdb files.
2001-02-08 14:04:08.50 spid1    Trying just primary files.
2001-02-08 14:04:08.50 spid1    Database 'tempdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
2001-02-08 14:04:08.50 spid1    Database 'tempdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Note There are multiple reasons as to why a database becomes marked suspect but those reasons are beyond the scope of this article.

WORKAROUND

The following steps explain how you can create a new tempdb database. With this workaround, you are able to successfully start the SQL Server service.
  1. If they exist, rename the current Tempdb.mdf and Templog.ldf files. If the files do not exist, that is one possible reason for the suspect status, the files are missing.
  2. Start SQL Server from a command prompt by using the following command:

    sqlservr -c -f -T3608 -T4022

    For SQL Server 2000 named instances you have to add the additional -s parameter.

    On a SQL Server 7.0 cluster, you must first run this statement:

    set _CLUSTER_NETWORK_NAME=YourSQLVirtualName

    This will allow SQL Server to start from a command prompt.

    Note Make sure that the command prompt window remains open after SQL Server starts. Closing the command prompt window terminates the SQL Server process.
  3. Connect to the server by using Query Analyzer, and then use the following stored procedure to reset the status of the tempdb database.
    exec master..sp_resetstatus Tempdb
  4. Shut down the server by pressing CTRL+C in the command prompt window.
  5. Restart the SQL Server service. This creates a new set of tempdb database files, and recovers the tempdb database.

    Note The environment variable creates a new set of tempdb database files when the SQL Server service is restarted. To remove the environment variable, run the following statement: 
    set _CLUSTER_NETWORK_NAME=

REFERENCES

For more information, see the "How to start a named instance of SQL Server (Command Prompt)" topic and the "SQL Server Startup Options" topic in SQL Server 2000 Books Online.
Modification Type:MajorLast Reviewed:12/14/2005
Keywords:kbprb KB288809
©2004 Microsoft Corporation. All rights reserved.