Microsoft KB Archive/288809

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

Article ID: 288809

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * 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 Edition

-



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

<div class="references_section">