SQL Database Repair

How to Recover MS SQL Database from Suspect Mode?

Written By Monika Dadool
Approved By Kuljeet Singh
Updated on 5 Nov, 2024
Min Reading 6 Min
Share

Table of Content

Summary: There are various factors and reasons responsible for the SQL database is in suspect mode issue. In this post, 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 recover the database from suspect mode if the reason is corruption in the database file.

Free Download100% secure

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.

Below, we’ll discuss why the SQL database goes into suspect mode and how to recover the database from suspect mode.

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 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 (SSM S). 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 the 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 databasetest13  
FROM DISK = 'Z:\SQLServerBackups\databasetest13.bak' ;

Next, try connecting to the database.

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

Method 2: 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.

Figure 1: Connect to Server Instance

Step 2: Select the New Query option.

Figure 2: 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 mode:

EXEC sp_resetstatus ‘db_name’;
ALTER DATABASE db_name SET EMERGENCY

Figure 3: Set Database in Emergency Mode

Note: If you are unable to set the database in Emergency mode, skip to the next solution.

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 (‘database_name’)

Figure 4: Check Database Consistency

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 database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Figure 5: Set Database to Single_User Mode

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 (‘database_name’, REPAIR_REBUILD)

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

DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

Figure 6: Repair Database with DBCC CHECKDB

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 database_name SET MULTI_USER

Figure 7: Set Database to Multi-User Mode

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

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. To avoid data loss and quickly repair the corrupt SQL database, you can use a professional SQL database repair tool, like Stellar Repair for MS SQL. The tool can repair the database (MDF/NDF) file and recovers 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).

Initial Screen of Stellar Repair for MS SQL

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

Repair selected corrupt database file with Stellar Repair for SQL software

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.

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

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

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 an 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 a specialized SQL database repair software, such as Stellar Repair for MS SQL that can repair the database without any data loss and helps you recover the database from suspect mode.

FAQ:

What should I do if I can’t recover the database using the standard T-SQL commands?

If T-SQL commands fail to repair the database, then you can use a professional SQL repair tool, like Stellar Repair for MS SQL. It can help you repair the database and recover all the data with complete integrity.

Can I prevent my SQL database from entering suspect mode?

The primary reason for the SQL database to enter into suspect mode is corruption in database file. So, follow the best practices to prevent corruption in your database file.

What does SQL database goes into suspect mode mean?

When your SQL database goes into suspect mode, it indicates that the recovery process has started but failed to complete. This happens due to corruption in the master database file.

When should I use the REPAIR_ALLOW_DATA_LOSS option with DBCC CHECKDB?

You can use the REPAIR_ALLOW_DATA_LOSS option, if DBCC CHECKDB recommends it as a repair option. However, this command leads to data loss.

Was this article helpful?

YES0

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