SQL Database Repair

How to Recover MS SQL Database from Suspect Mode?

Written By Monika Dadool
Approved By Kuljeet Singh
Updated on 24 Feb, 2026
Min Reading 6 Min
Share

Table of Contents

Summary: There are various factors and reasons responsible for the SQL database is in suspect mode issue. In this guide, you will find the solutions to recover the SQL database from suspect mode. You will also get to know about an advanced SQL database repair tool that can help resolve the issue if it has occurred due to corruption in the database file.

Free Download100% secure

After a long weekend, when I resumed office, I found myself in a difficult situation. There is a SQL database named BusinessOperation which went into the Suspect mode, halting all the operations. It contains some business critical information. As the first basic step, I restarted the SQL Server. Then, checked the status of database by running this command:

SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS') AS Status;

GO

Recover MS SQL Database from Suspect Mode

Unfortunately, it was still in Suspect mode. I was not able to find out how to come out of this situation. So, I consulted other DBAs on the issue and they suggested some troubleshooting steps. I was able to recover my database from suspect mode by following those steps. Based on this experience, I have prepared this guide on how to recover MS SQL database from Suspect mode. Let’s start from the basic.

What is Suspect Mode in SQL Server?

Suspect mode is one of the states of MS SQL Server database. The SQL database goes into suspect mode when the database recovery fails or the primary data files are either missing or corrupted. In suspect mode, the database becomes inaccessible. This means you can neither connect to the database nor recover it during server startup. The database will remain in the suspect mode until it is restored to the normal state.

Why SQL Database goes into Suspect Mode?

The database may go into suspect mode due to one or more of the following reasons:

The system’s hard disk where database is stored is out of space.
The MDF database file is corrupted or damaged.
The primary file group of the database is corrupted or missing.
The transaction log file is inaccessible or corrupted.
MS SQL Server shuts down or crashes in the middle of a transaction.
The database is terminated unexpectedly.
SQL is unable to complete a rollback or roll-forward operation.

Solutions to Recover SQL Database from Suspect Mode

You can first check the database status. For this, open the SQL Server Management Studio (SSMS), go to the Object Explorer, and check the database. If the database is in suspect mode, check the SQL Server error log in SQL Server Management Studio (SSMS). This log contains all user-defined events and system events that will help you find the exact cause behind the database is in suspect mode issue. If corruption is the reason behind the database in the suspect mode issue, then follow the given methods.

Method 1: Restore Database from Backup

If the database is corrupted or damaged, the easiest solution is to restore SQL database from backup. If you have an updated database backup file, then you can restore the backup file. To restore the database backup file, follow these steps:

Open SQL Server Management Studio (SSMS) and connect to your server instance.
From the standard bar, click ‘New Query’.
In the ‘Query Editor’ window, type the below command:

RESTORE DATABASE BusinessOperation

FROM DISK = 'Z:\SQLServerBackups\ BusinessOperation .bak' ;

Next, try connecting to the database.

If your backup file is not updated or available, then follow the next method.

Method 2: Turn Off the Suspect Tag

You can also try to turn off the suspect tag by using the EXEC sp_resetstatus ‘dbNSFP’ command when your database enters suspect mode due to corruption or missing files. It is a stored procedure in the SQL Server system. This will reset the status of database from suspect to online. However, it does not resolve the corruption issue in database. Run this command to turn off the suspect tag:

EXEC sp_resetstatus ‘BusinessOperations’;

It returns 0 if the command completes successfully and shows the message “The suspect flag in the database “businessOperations” is already reset.” If the database still fails to come online, then you can try the next method.

Method 3: Use DBCC CHECKDB Command to Repair the Database

You can use the DBCC CHECKDB command to repair the damaged SQL database and restore it from suspect mode to online mode. Following are the steps to repair the database using the DBCC CHECKDB command:

Note: It is suggested to create a backup of the SQL database before running this command.

Step 1: Open SSMS and connect to the server instance.

Step 2: Select the New Query option.

Step 3: In the Query Editor window, enter the following code to set the database to EMERGENCY mode:

ALTER DATABASE db_name SET EMERGENCY

For example:

ALTER DATABASE BusinessOperations SET EMERGENCY

Step 4: Now, run the following DBCC CHECKDB command. This command will check the integrity/consistency of the database. It will also report the consistency errors (if found) in the database and recommend the repair option.

DBCC CHECKDB (BusinessOperations)

Step 5: Next, set the database to Single User mode to prevent other users from making any changes to the database during the repair process. To set the database to Single User mode and roll back the previous transactions, execute the below command:

ALTER DATABASE BusinessOperations SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 6: After putting the database in SINGLE USER mode, run the DBCC CHECKDB command with the suggested repair option. If the REPAIR_REBUILD option is suggested, then run DBCC CHECKDB command as given below. This option can quickly repair missing rows in non-clustered indexes.

DBCC CHECKDB (‘BusinessOperations’, REPAIR_REBUILD)

If the REPAIR_ALLOW_DATA_LOSS is suggested as a minimum level of repair, then run the command as given below:

DBCC CHECKDB (BusinessOperations, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

Note: The DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option usually helps to bring the database to a consistent state. But, this command results in data loss.

Step 7: When the database is repaired, bring it back to Multi-User mode. To do so, execute the below command:

ALTER DATABASE BusinessOperations SET MULTI_USER

Now, you should be able to connect to the database.

Sometimes, DBCC CHECKDB fails to repair the database and displays an error like:

Msg 945, Level 14, State 2, Line 1
Database 'BusinessOperations' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

To fix this error, read our guide on How to Fix SQL Error 945 – Database cannot be Opened due to Inaccessible Files.

Method 3: Use a Professional MS SQL Repair Tool

The DBCC CHECKDB command can repair the database and helps you recover the database from suspect mode. However, it does not guarantee the accuracy of repaired data in the database or may fails in certain scenarios. To avoid data loss and quickly repair the corrupt SQL database, you can use Stellar Repair for MS SQL – a professional SQL database repair tool. The tool can repair the database (MDF/NDF) file and recover all the objects, including tables, triggers, stored procedures, and indexes with complete precision and integrity. It can help fix common SQL database corruption errors. The software is compatible with SQL 2022, 2019, 2017, and earlier versions.

Let’s see how to repair SQL database with Stellar Repair for MS SQL and recover it from suspect mode.

Note: Close the server instance before running the 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).

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

Note: Uncheck the ‘Include Deleted Records’ checkbox if you don’t want the deleted records to be recovered.

Step 4: When the repair process is complete, it will display a “Selected MS SQL database repaired successfully” message. Click OK.

Step 5: The software will present all the recoverable SQL database objects. You can preview the objects.

Preview recoverable SQL database objects

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

Step 7: On the Save Database window, under the Saving Option, select New Database, Live Database, or Other Formats. Click Next.

Step 8: On the next window, fill in the details required under Connect To Server and click on the Next option.

Step 9: Select the Save Mode and click Save.

When the database is repaired, open SSMS and attach the repaired database file. You will now be able to access the database.

Conclusion

Above, we have discussed the solutions 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 a healthy or updated backup, then use the DBCC CHECKDB command to repair it. However, the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option can result in data loss. A better option is to use Stellar Repair for MS SQL – a specialized SQL database repair software that can repair the database without any data loss and help you recover the database from suspect mode.

Read Suspect Mode Case Study: St. Robert, MO, USA Police Department Repairs Large MS SQL Database by using Stellar Toolkit for MS SQL

How to fix database in suspect mode issue in Always On Availability Group?

To resolve the database in suspect mode issue in Always on Availability Group setup, first check the database health and then remove it from the Availability Group. After this, restore the database from backup or use a professional SQL database repair tool to repair it.

About The Author

Monika Dadool

Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing. She is a tech enthusiast and expert who specializes in writing about SQL Server, MySQL Server, MariaDB Server, Microsoft Access, Active Directory, email recovery, Microsoft 365, pattern recognition, machine learning, data recovery, file repair, and operating systems like Linux, Windows, and Mac. She also writes about accounting software such as QuickBooks and Sage 50, as well as web-scripting languages like HTML, JavaScript, Python, PHP, Visual Basic, ASP.NET, and AJAX. Monika is passionate about researching and exploring new technologies, and she enjoys developing engaging technical blogs that help organizations and database administrators resolve various issues. When she's not creating content, you can find her on social media, watching web series, reading books, or exploring new food recipes.

35 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