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

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s