Article ID: 288809 - Last Review: February 22, 2007 - Revision: 5.3

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

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q288809

Expand all | Collapse all

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.

Back to the top

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.

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.
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.
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
Shut down the server by pressing CTRL+C in the command prompt window.
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=

Back to the top

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.

Back to the top