Stellar Knowledge Base

Procedure to Recover SQL Database from SUSPECT Mode

Author : Creative Team Updated on September 21, 2017

SQL Server Database in suspect mode is a clear indication that you cannot access the database or no transactions are possible until it back to the online. You might have experienced that your SQL database is marked as SUSPECT. It happens due to several reasons: transactional log file of a database is corrupt, faulty hardware, missing transaction log file of the database, virus attack, improper shutdown of SQL server and much more.  Look in the error log of your SQL server to know the specific cause for your database. Error log tells the exact cause why your database has gone into suspect mode.

 

In order to resolve this problem, you need to change the database status to EMERGENCY mode that provides a read-only access to the administrator. The basic purpose of changing the database mode to emergency is troubleshooting.  For changing the database status to EMERGENCY mode, run the below T-SQL query:

ALTER DATABASE  dbName  SET  EMERGENCY

Once you have changed the database status, now administrator can access it. Next step is DBCC CHECKDB execution. DBCC CHECKDB checks all logical and physical integrity of the specified database. If it finds any problem with the database then recommend appropriate repair options: repair_rebuild, repair_fast, or repair_allow_data_loss.

DBCC CHECKDB('dbName')

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

ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Now, Re-Run DBCC CHECKDB with repair options to repair the suspect database.

DBCC CHECKDB (dbName, REPAIR_ALLOW_DATA_LOSS)

Note that this DBCC CHECKDB with repair_allow_data_loss  will fix all the errors and repair them; however, it may cause data loss from the database.

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

Suggestion: This is what you need to do to fix suspected database issue. Moreover, run DBCC CHECKDB command regularly to check for physical and logical integrity of the database.

Another workaround includes restoring the database from clean and updated backup that you have prepared before the problem occurs. It is the quick and best method to fix the problem.

However, you may face a situation when you do not have a most recent backup of database or backup is corrupt itself and database have been marked as 'SUSPECT'. In this case, an ultimate solution is to use Stellar Phoenix SQL Database Repair that is designed to recover database from the 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 2016.


 

2 User Comments

 

Prajith
August 24, 2017 10:32 am

Very Helpful

john
September 11, 2017 01:12 pm

Quite a skin saver

 

User Comments

 



 

 

Stellar Phoenix & Stellar Data Recovery are Registered Trademarks of Stellar Information Technology Pvt. Ltd.
© Copyright 2017 Stellar Information Technology Pvt. Ltd. All Trademarks Acknowledged.