sp_resetstatus turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in sys.databases. The SQL Server error log should be consulted and all problems resolved before running this procedure. Stop and restart the instance of SQL Server after you execute sp_resetstatus.
A database can become suspect for several reasons. Possible causes include denial of access to a database resource by the operating system, and the unavailability or corruption of one or more database files.
That doesn't make db in working status, have to find out why db is in suspect and fix the problem.
Where do we learn how to fix the problem?
For example, due to a power outage I have torn pages. I ran DBCC CHECKDB ('my database') WITH ALL_ERRORMSGS, NO_INFOMSGS and it gave me a bunch of cryptic error messages suggesting that some rows had foreign keys that did not exist in the parent table. Most of the error messages were in terms of "object ids". I tried running CHECKDB in EMERGENCY MODE with REPAIR_ALLOW_DATA_LOSS -- though I was able to see the database in QueryAnalyzer it apparently did not delete the offending rows.
Do I need to identify the offending rows by hand? If so, how do I go about doing that?
Database marked as suspect - bring online?
Posted - 02/18/2008 : 09:28:12
