How to Recover MS SQL Database from Suspect Mode?

Written By Charanjeet Kaur
Approved By Kuljeet Singh
Updated on 25 Jun, 2024
Min Reading 6 Min
Share

Table of Content

Summary: Read this post to find solutions to recover MS SQL database marked as suspect. It describes step-wise instructions to fix the ‘SQL server suspect database’ issue by running Transact-SQL (T-SQL) commands in SQL Server Management Studio (SSMS). Also, it provides an alternative solution to restore the database using a SQL Recovery tool.

Free Download100% secure

When SQL database goes into suspect mode, it becomes inaccessible. In such a situation, you will neither be able to connect to the database nor recover it during server startup.

Recover MS SQL Database from Suspect Mode — Figure 1: Database in Suspect Mode

Check out the Infographic below for quick solutions to recover database from suspect mode in SQL Server 2008, and higher versions.

When does SQL database goes to suspect mode?

When SQL server suspects the primary filegroup of the database to be damaged or if the database file is missing, the database status is set to ‘Suspect’.

Also, there are a wide range of errors that could result in SQL database in suspect mode. Some of them are listed as below:

System fails to open the device where the data or log file of SQL server resides.
SQL server crashes or restarts in the middle of a transaction, resulting in a corrupt or inaccessible transactions log file.
SQL Server tries to open a database, and the file belonging to that database is already open by anti-virus software installed on your system.
The database is terminated abnormally.
Lack of disk space.
SQL cannot complete a rollback or roll forward operation.
Database files are being held by the operating system, third-party backup software, etc.

How to get SQL database out of suspect mode?

NOTE: You can try restoring the database in suspect mode from a good known backup. If the backup is not available, proceed with the following steps.

Follow the steps in sequence given below to recover MS SQL database from suspect mode:

Step 1: Open SSMS and connect to the database.

Connect to SQL Server Database in SSMS — Figure 2: Connect to Database

Step 2: Select the New Query option.

Select New Query to open Query Window in SSMS — Figure 3: Select New Query

Step 3: In the Query editor window, enter the following code to turn off the suspect flag on the database and set it to EMERGENCY:

EXEC sp_resetstatus ‘db_name’;
ALTER DATABASE db_name SET EMERGENCY

Set SQL Database in Emergency Mode — Figure 4: Set Database in Emergency Mode

NOTE: If you cannot set the database in emergency mode, skip to the next solution.

Step 4: A suspect database might not be corrupted. You can determine if the database is corrupted or not by running the following DBCC CHECKDB command.

DBCC CHECKDB (‘database_name’)

This statement will report any consistency errors (if found) in the database and will recommend running the minimum level of repair option to fix corruption.

Before initiating the repair process, you must first set the database into ‘Single User Mode.’ Doing so will prevent other users from making any changes to the database during the repair process.

Check SQL Database Consistency — Figure 5: Check Database Consistency

Step 5: Now, let’s bring the database into the Single User mode and roll back the previous transactions by executing the below command:

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Set SQL Database to Single User Mode — Figure 6: Set Database to Single_User Mode

Step 6: Take a complete backup of the corrupted files to avoid chances of data loss.

Step 7: After putting the db in SINGLE USER mode, try to fix the consistency errors using the REPAIR_REBUILD option of DBCC CHECKDB. This option can quickly repair missing rows in nonclustered indexes. In addition, you can use it for more time-consuming repair operation, such as rebuilding an index.

DBCC CHECKDB (‘database_name’, REPAIR_REBUILD)

However, if REPAIR_ALLOW_DATA_LOSS is suggested as minimum level of repair, then run DBCC CHECKDB with the suggested repair option. The syntax is as follows:

DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

Use DBCC CHKDB command with Repair Option — Figure 7: Repair Database with DBCC CHECKDB

Step 8: Bring the database into the Multi-User mode:

ALTER DATABASE database_name SET MULTI_USER

Set SQL Database to Multi User Mode — Figure 8: Set Database to Multi-User Mode

ALTER DATABASE database_name SET MULTI_USER

Step 9: Refresh the database server.

After completing these steps, you should be able to connect to the database. In case of any data loss, you’ll have the db backup to restore from (Step 6).

What if this solution doesn’t work?

If your server database file has turned severely corrupt, the above-mentioned steps may fail to revive the database. At this point, try restoring the database by using Stellar Repair for MS SQL.

The software can fix common SQL database corruption errors that occur due to reasons such as the database in suspect mode and several others. The software uses advanced algorithms to repair and restore SQL db from suspect mode to normal state (online).

How to Recover SQL Database from Suspect Mode with the Stellar SQL Recovery Tool?

NOTE: Make sure to close the server instance before running Stellar Repair for MS SQL software.

Step 1: Download, install, and run Stellar Repair for MS SQL software.

Step 2: From the Select Database window, choose Browse or Search to select the SQL database file (.mdf) of the suspect database.

Initial Screen of Stellar Repair for MS SQL — Figure 9: Select Database File

Step 3: Once the file is selected, hit Repair.

Repair selected corrupt database file with Stellar Repair for SQL software — Figure 10- Repair Selected File

NOTE: Make sure to uncheck the ‘Include Deleted Records’ checkbox if you don’t want the deleted records to be recovered.

Step 4: Preview the repaired MDF file for recoverable SQL server database objects.

Preview repaired database components — Figure 11: Preview window

Step 5: Click Save on File menu to save the repaired file.

Select save option for saving repaired database file — Figure 12: File menu

Step 6: From Save Database window, perform the following:

Select MDF under Save As.
Save the repaired file in New database or Live database.
Fill in the details under Connect To Server.

Add details to save repaired database file — Figure 13: Save Database window

Step 7: Click Save.

Open SSMS and attach the db (containing the repaired MDF file). You will be able to access the database.

Additional features of the software

Repairs corrupt MDF and NDF files.
Recovers tables, triggers, keys, indexes, stored procedures, defaults, rules, schema, etc.
Supports MS SQL 2019, 2017, 2016, 2014, 2012, 2008 R2, and lower versions.
Provides multiple saving options to save the repaired database including MS SQL (MDF), CSV, HTML, and XLS.

The software is trusted by Microsoft MVPs

Conclusion

This post discussed methods on ‘How to recover MS SQL database from suspect mode’. The best approach is to restore the database from a healthy backup. If you don’t have backup, use the EMERGENCY mode to access the database and repair it. However, you may fail to rollback the transactions that were active when database went into suspect mode. Also, using the REPAIR_ALLOW_DATA_LOSS option as the minimum repair level can lead to data loss. A better alternative is to use a specialized SQL database repair software that helps repair and restore the database from suspect to a normal state.

Was this article helpful?

YES0

About The Author

Charanjeet Kaur

Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.

34 comments

Bruzote says:

March 23, 2019 at 3:52 pm

The putting into an emergency is fine, but it can suspect for many reasons that others may not understand. Try all else including putting online, normal restore or page restores before running such a severe option.

As a SQL DBA that is the LAST resort and even then I would think long and hard about doing it.

Reply
1. Eric Simson says:
  
  March 25, 2019 at 4:59 am
  
  Your inputs will be helpful for the SQL admins because data is critical for everyone.
  
  Reply
Satish says:

December 5, 2018 at 11:12 am

my one of database is came into emergency mode from suspect and now it is saying that database is in use and i am not able to move it further for resolution. i already tried below steps.

Turn off the suspect flag on the database and set it to EMERGENCY
EXEC sp_resetstatus ‘db_name’;
ALTER DATABASE db_name SET EMERGENCY

Perform a consistency check on the master database
DBCC CHECKDB (‘database_name’)

Bring the database into the Single User mode and roll back the previous transactions
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Take a complete backup of the database
Attempt the Database Repair allowing some data loss
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

Bring the database into the Multi-User mode
ALTER DATABASE database_name SET MULTI_USER

Reply
1. Eric Simson says:
  
  December 5, 2018 at 4:55 am
  
  We request to try a free demo of SQL database repair software and share your findings with us.
  
  Reply
Amely says:

November 27, 2018 at 11:39 am

Hi,

My Database was in the Suspect mode. I followed all ‘Steps to Fix the SQL Server Database Suspect Mode Error’. But I’m already waiting 35 hours to complete the query.

Could this be because of a large database?

Reply
1. Eric Simson says:
  
  November 28, 2018 at 4:38 am
  
  Apart from databse size, soemtimes a severe level of error/bug in SQL server file is responsible for long waiting time to complete the query.
  
  You can also take assitance of SQL Database Repair software to make your databse file error-free.
  
  Reply
Lincoln says:

June 27, 2018 at 4:39 am

We did four attempts but failed to fix the suspect issue in SQL. Finally, we solved this error using Stellar solution for SQL.

Thank YOU!

Reply
1. Eric Simson says:
  
  June 29, 2018 at 6:27 am
  
  Good!
  
  We hope that you can recommend this tool to others.
  
  Reply
Ron Oz says:

June 4, 2018 at 1:09 pm

SQLServer 2014 Enterprise Edition, Windows 2012

USE [master]
ALTER DATABASE db_name SET EMERGENCY – Completed
DBCC CheckDB ([MyDB_Prod] — Completed
ALTER DATABASE MyDB_Prod SET SINGLE_USER WITH rollback immediate; — Completed
DBCC CheckDB ([MyDB_Prod], REPAIR_ALLOW_DATA_LOSS); — Failed due to below error.
ALTER DATABASE [MyDB_Prod] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [MyDB_Prod] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Messages:

Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 924, Level 14, State 1, Line 5
Database ‘MyDB_Prod’ is already open and can only have one user at a time.

Questions:

1. the DB is in single_user mode, why it’s throwing error that it can only be used one user at a time. What should be the status of the DB when running
DBCC CheckDB ([MyDB_Prod], REPAIR_ALLOW_DATA_LOSS);?

2. I tried to shutdown the DB MyDB_Prod but it doesn’t allow me. How can i shutdown the DB?

3. If i’m able to shutdown how can i start and put back into a status where i can run

DBCC CheckDB ([MyDB_Prod], REPAIR_ALLOW_DATA_LOSS);

Kindly email me please as i need to resolve this issue as soon as possible. Appreciate your hep in advance.

Thank you,

Reply
1. Priyanka Chauhan says:
  
  June 4, 2018 at 4:47 am
  
  Hi Ron,
  
  Msg 924, Level 14, State 1, Line 5
  Database ‘MyDB_Prod’ is already open and can only have one user at a time.
  
  The level 14 belongs to security level errors like permission denied. You can verify the reason by using sp_who or sp_who2 stored procedures.
  Read the complete post: Read More
  
  Let me know, if you have further query.
  
  Reply
Bob says:

April 5, 2018 at 3:28 am

Hi,

I am a SQL database administrator from NJ. I have 3 NDF files associated with MDF file. Will it work on this situation?

Let me know.

Reply
1. Priyanka Chauhan says:
  
  April 5, 2018 at 4:52 am
  
  Dear Bob,
  
  Stellar Repair for MS SQL software repairs the data from MDF as well as associated NDF files. So, if you have one or more NDF files associated with the database then, our SQL recovery software will work on this situation.
  
  Reply
Hari says:

February 21, 2018 at 5:02 am

Solution perfectly works fine.

Reply
1. Jyoti Prakash says:
  
  February 23, 2018 at 4:40 am
  
  Hello Hari,
  
  Thanks for your feedback.
  
  Reply
chandrapal says:

January 10, 2018 at 6:20 am

Yes..!
Good solution.

Reply
1. Jyoti Prakash says:
  
  January 19, 2018 at 3:29 am
  
  Hello Chandrapal,
  
  Thanks for your valuable feedback
  
  Reply
Christopher Cole says:

December 14, 2017 at 11:32 am

Does your software support MSSQL 2016?

Reply
1. Eric Simson says:
  
  December 15, 2017 at 3:24 am
  
  Yes!
  
  You can also verify this feature using free demo copy.
  
  Reply
Steve says:

September 28, 2017 at 3:50 am

Getting a database out of suspect is always a very straining task and can lead to more and more complexities but thanks to your informative article, I successfully fixed the issue.

Reply
1. Priyanka Chauhan says:
  
  September 28, 2017 at 11:06 am
  
  Dear Steve,
  
  Thanks for your valuable comment.
  It’s good to know that, our SQL database repair software fixed your issue.
  
  Keep reading.
  
  Reply