Procedure to Recover SQL Database from SUSPECT Mode

Sometimes, when connecting to an SQL Server, you may find that the SQL database (db) is marked as ‘SUSPECT’.

This may happen due to several reasons like missing or corrupt transactional log file of the database, faulty hardware, virus attack, abrupt shutdown of SQL server, etc. View the SQL Server error log to know the specific cause behind SQL Server database suspect mode.

Quick Solution: There is a quick way to fix SQL database SUSPECT mode issue. Download the SQL repair software and repair the SUSPECT database and make it ONLINE without any downtime.

When SQL Database goes in suspect mode, you cannot access the db and cannot perform any transactions until it is back online. In order to resolve this problem, follow these steps:

Step 1: Put the database in EMERGENCY mode

Change the database status to EMERGENCY mode. Doing so, will provide users read-only access to the database. The basic purpose of changing the database mode to emergency is troubleshooting what resulted in database suspect mode issue. 

To change the database status to EMERGENCY mode, run the below T-SQL query:

ALTER DATABASE  dbName  SET  EMERGENCY

Once you have changed the database status, you will be able to access the db.

Step 2: Check database integrity

Next, execute Database Console Command (DBCC) CHECKDB. The command helps check the logical and physical integrity of the specified database. If it finds any problem with the database, it recommends appropriate repair options such as ‘REPAIR_REBUILD’, ‘REPAIR_FAST’, or ‘REPAIR_ALLOW_DATA_LOSS’.

Use the following query to run DBCC CHECKDB:

DBCC CHECKDB ('dbName')

Suggestion: Make sure to run the DBCC CHECKDB command regularly to check for physical and logical integrity of the database.

Step 3: Set database in SINGLE_USER mode

Now run the below T-SQL query to rollback any transaction and bring the SQL database to Single User mode.

ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 4: Repair the database

Re-Run DBCC CHECKDB with repair options to repair the suspect database. For this, use the following T-SQL query:

DBCC CHECKDB (dbName, REPAIR_ALLOW_DATA_LOSS)

Note: DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS may help you fix all the errors and repair them; however, it can cause data loss from the repaired database.

Step 5: Bring the database to MULTI_USER mode

If you find that the script runs successfully, bring the database back from single-user mode to multi-user mode. Run the following command to do it:

ALTER DATABASE dbName SET MULTI_USER

Step 6: Bring the database ONLINE

To change the database state from EMERGENCY to ONLINE, execute the following command:

ALTER DATABASE dbName SET ONLINE

Following the above steps will likely help you fix SQL database suspect mode issue. If the problem still persists, restore the database from clean and updated backup that you created before the database in suspect mode problem. Restoring db from backup is the best and fastest method to fix the problem.

For detailed information on recovering MS SQL Database from suspect mode, refer to this link.

If you do not have the most recent backup of database or backup is corrupt, using Stellar Repair for MS SQL software can help. The software is designed to recover database from suspect mode, as well as repair, damaged or corrupt MS SQL Server database (.mdf & .ndf) files. It supports all latest version of SQL server including SQL server 2019.