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

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.
Propriedades

ID do Artigo: 288809 - Última Revisão: 10 de jul de 2008 - Revisão: 1

Comentários