Stellar Knowledge Base

Procedure to Recover SQL Database from SUSPECT Mode

 Modified on: June 22, 2020 |    123059 views

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.

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:

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:

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:

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:

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:

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

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.

 

10 User Comments

Very Helpful

Prajith

Quite a skin saver

john

Working

Rashid Khan

support

Hi,

Thank you for your comment.

Kindly let us know your exact issue in detail so that we can assist you.

Warm Regards

THANKS A LOT FOR YOUR HELP. VERY MUCH APPRECIATED. YOU CERTAINLY HELPED !!!!!!

JOSE ACOSTA

what if my db name has space within it
ex: my db name is : Doha Bank

shouna

Stellar support

Hi,

Thank you for your comment.

Kindly let us know your exact issue in detail so that we can assist you.

Warm Regards

Thanks a mill. You really save my life!

Kawita

Client had a power outage + this Worked Perfectly !

JDNKNG

Rajeev

Hi,

Thank you for your comment.

We are very happy to see that the application has successfully resolved the issue.

In case you need any further assistance then use the below link to connect with our expert:

https://www.stellarinfo.com/support/

Warm Regards
Stellar Data Recovery

 

User Comments


 

 

Stellar Repair for MS SQL Stellar Repair for MS SQL is most recommended software by MSSQL Administrators. The Software fixes corrupt SQL database files and recovers inaccessible objects to save business data.

Learn More

 

Related Articles