Reason for database to go into suspect mode:
1. Data
files or log files are corrupt.
2. Database
server was shut down improperly
3. Lack
of Disk Space
4. SQL
cannot complete a rollback or roll forward operation.
How to recover database from suspect mode:
1. Change
the status of your database. Suppose database name is “BluechipDB”
EXEC sp_resetstatus '' ;
Example: EXEC
sp_resetstatus 'BlueChipDB'
2.
Set the database in “Emergency”
mode
ALTER DATABASE <Database Name> SET EMERGENCY;
Example: ALTER
DATABASE BlueChipDB
SET EMERGENCY
3.
Check the database for any inconsistency
DBCC CHECKDB('' );
Example: DBCC
checkdb('BlueChipDB')
4. If you get any error after executing
DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running
following query. If no error found then you need not execute the following
query.
ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK
IMMEDIATE;
Example: ALTER
DATABASE BlueChipDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
5. For safety, take the backup of the
database.
6. Run the following query as next step.
Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a
one way operation that is once the database is repaired all the actions
performed by these queries can’t be undone. There is no way to go back to the
previous state of the database. So as a precautionary step you should take
backup of your database in step 5 mentioned above.
DBCC CHECKDB ('' ,
REPAIR_ALLOW_DATA_LOSS);
Example: DBCC
CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)
7. Finally, bring the database in MULTI
USER mode
ALTER DATABASE <Database Name> SET MULTI_USER;
ALTER DATABASE [BlueChipDB] SET MULTI_USER
8. Refresh your database server and verify the connectivity of
your database. Now users should be able to connect to the database properly. If
any data loss, you can restore database – backup taken in step 5.
