How to Recover MS SQL Database from Suspect Mode?
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.
Contents
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.
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.
Step 2: Select the New Query option.
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
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.
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
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)
Step 8: Bring the database into the Multi-User mode:
ALTER DATABASE database_name SET MULTI_USER
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.
Step 3: Once the file is selected, hit Repair.
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.
Step 5: Click Save on File menu to save the repaired file.
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.
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.
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.
Your inputs will be helpful for the SQL admins because data is critical for everyone.
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
We request to try a free demo of SQL database repair software and share your findings with us.
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?
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.
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!
Good!
We hope that you can recommend this tool to others.
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,
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.
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.
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.
Solution perfectly works fine.
Hello Hari,
Thanks for your feedback.
Yes..!
Good solution.
Hello Chandrapal,
Thanks for your valuable feedback
Does your software support MSSQL 2016?
Yes!
You can also verify this feature using free demo copy.
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.
Dear Steve,
Thanks for your valuable comment.
It’s good to know that, our SQL database repair software fixed your issue.
Keep reading.