How to resolve suspect Database issue (Emergency Repair) SQL Server

There are several reasons why database goes into suspect mode, but best way to resond that I found very helpfull

1) Set the database to emergencey mode

ALTER DATABASE ‘db name’ SET EMERGENCY

2) Set the database to Single user mode so to do dbcc repair

ALTER DATABASE DAXBI SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

3) Run dbcc repair command

DBCC CHECKDB (N’db name’, REPAIR_REBUILD ) WITH ALL_ERROMSGS, NO_INFOMSGS;

that will take care of any consistency errors with indexes and partitions

if you want the database to be back online not worried about the data loss

DBCC CHECKDB (‘db name’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERROMSGS, NO_INFOMSGS;

4) Set databsae to multi user mode

ALTER DATABASE ‘db name’ SET MULTI_USER

Advertisements