How to repair Suspect Databse in SQLServer?

How to repair Suspect Databse in SQLServer?

If you are supporting SQL Server you may see a database in Suspect mode in Production/Non Production, in suspect mode users canot connect to db and they canot perform any transaction.

In what circumstances a healthy databse goes in to suspect mode.

1. Data/Log file corruption triggers
2. if SQL cannot complete a rollback or rollforward operation
3. Power failure at SQL Server
4. If someone restarts sql services in middle of data loading process to tables through bulkinsert/SSIS

Now we will see how to bring back the database to normal state, first we need to put the database in “Emergency” mode and see any data pages/extents got corrupted by running dbcc checkdb command  if see any repair with data loss and bring back it to normal state.

Example: “ABC” is our Database which is actually gone in to suspect mode.

Connect to SQL Server through SSMS and open Query Analyzer and execute following commands.

EXEC sp_resetstatus 'ABC'

ALTER DATABASE ABC SET EMERGENCY

DBCC checkdb('ABC')

ALTER DATABASE ABC SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('ABC', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE ABC SET MULTI_USER