SQL SERVER - Resolving database in SUSPECT mode

SQL SERVER – Resolving database in SUSPECT mode

SQL Server runs in different modes, and can be in a specific state at a given time.

The modes are:

In this blog post, we will talk about how to deal with a database that is in SUSPECT mode.

Here are some of the reasons why SQL Server marks a Database as Suspect:

How to Recover SQL Server from SUSPECT Mode

1.Turn off the suspect flag on the database and set it to EMERGENCY

EXEC sp_resetstatus 'YourDBName'; 
ALTER DATABASE YourDBName SET EMERGENCY

2. Perform a consistency check

DBCC CHECKDB YourDBName

3. Bring the database into the Single User mode and roll back the previous transactions

ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Take a complete backup of the database

5. Attempt the Database Repair allowing some data loss

DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS)

6. Bring the database into the Multi-User mode

ALTER DATABASE YourDBName SET MULTI_USER

7. Refresh the database server and verify the connectivity of the database

Cheers!

SQL SERVER – Resolving database in SUSPECT mode