Database Suspect

1)      What is suspect database?

A database which is non-operational and tagged as suspect.

2)   What are the reasons for a database to suspect mode?

§  Improper shutdown of the SQL Server.

§  Data files or log files are corrupted.

§  No enough space for recovery process at the time of startup of the SQL Server.

§  Files are not accessible because of the network permissions.

 

3)      How to recover the database from suspect mode?

·         Change the status of the database.

EXEC SP_RESETSTATUS '[my database]'

·         Set the database in emergency mode.

ALTER DATABASE [my database] set EMERGENCY

·         Check for inconsistency.

DBCC CHECKDB ('[my database]')

·         Set the database in single user mode.

ALTER DATABASE [my database] set SINGLE_USER with ROLLBACK IMMEDIATE

·         To remove all consistency errors (Repair database).

DBCC CHECKDB ('[my database]', REPAIR_ALLOW_DATA_LOSS)

·         Set the database to multiuser mode.

ALTER DATABASE [my database] set MULTI_USER

 

Now the database will be back to online. If the customer wants the 100% data, we can restore from backups.