Aug 25, 2009

SQL 2000: Database in suspect mode.

Database can go into suspect mode for a couple of reasons, including, corruption in data files, indexes, logs, bad storage, less space available on storage for the database files to grow. Following is one of the ways to fix the suspect databases. I followed this process on one of the production user databases.
Please read through before taking any action.
First you need to put your database into Emergency mode so that database can be accessed (though read only).

sp_configure 'allow updates', 1
Reconfigure with override
GO -- This will make system table editable.
update master..sysdatabases set status = status | 32768
where name = 'db'
GO --puts database into emergency mode

Once database is in emergency mode, try running the DBCC CHECKDB to check the database. If DBCC shows any error message, fix the error.

In case DBCC reports Log error, you may rebuild the log, however rebuilding the log will remove the t-log data:
DBCC REBUILD_LOG('db','c:\mssql\data\db.ldf')

In case of corrupted index, try creating the infected index again, you may try Create Index ... DROP_EXISTING

If nothing helps, you may run DBCC CHECKDB('DB',REPAIR_ALLOW_DATA_LOSS), however, this may certainly result in some data loss.

Once you have completed all the activities and database is fixed, you may run the following script to set database to normal mode.

update master..sysdatabases set status = 0 (Or the original value)
where name = 'db'
GO --puts database back into normal mode.

Sp_configure 'allow updates', 0
Reconfigure with override
GO -- This will make system tables un-editable.



  1. If you are unable to repair database from suspect mode then you can use third party tool which can help you to easily repair and recover database from suspect mode. Read more:

  2. Much obliged for sharing this brilliant substance. its extremely fascinating. Numerous web journals I see these days don't actually give whatever pulls in others however the manner in which you have plainly clarified everything it's truly awesome. There are loads of posts But your method of Writing is so Good and Knowledgeable. continue to post such helpful data and view my site too...
    how to make a paper airplane eagle | how to make a boomerang airplane | the eagle paper airplane | best paper airplane design for distance and accuracy | best paper airplanes for distance and speed | what is the best paper airplane design for distance | paper airplane that flies far and straight | nakamura lock paper airplane instructions | paper airplane templates for distance | science behind paper airplanes


Optimizing Indexes with Execution Plans

Contact Me


Email *

Message *