Home  >  Database Recovery  >  How to Recover MS SQL Database from Suspect Mode?

How to Recover MS SQL Database from Suspect Mode?

Jyoti Prakash Updated on January 8, 2021 Database Recovery 30 Comments |   5 min read

Summary: Read this post to find solutions on ‘how to recover MS SQL database from Suspect Mode?’. 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 by using a SQL Recovery tool. 

Sometimes when connecting to a SQL Server database, you may find that the database is marked as Suspect.

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

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 able to recover it during server startup.

Recover MS SQL Database from Suspect Mode

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:

  1. System fails to open the device where the data or log file of SQL server resides.
  2. SQL server crashes or restarts in the middle of a transaction, resulting in a corrupt or inaccessible transactions log file.
  3. 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.
  4. The database is terminated abnormally.
  5. Lack of disk space.
  6. SQL cannot complete a rollback or roll forward operation.
  7. 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:

Set SQL Database in Emergency Mode
Figure 4: Set Database in Emergency Mode
EXEC sp_resetstatus ‘db_name’;
ALTER DATABASE db_name SET EMERGENCY

Step 4: Perform a consistency check on the master database. To do so, copy and paste the following code in the query editor:

Check SQL Database Consistency
Figure 5: Check Database Consistency
DBCC CHECKDB (‘database_name’)

Step 5: Next, bring the database into the Single User mode and roll back the previous transactions by using the code:

Set SQL Database to Single User Mode
Figure 6: Set Database to Single_User Mode
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 6: Take a complete backup of the database.

Step 7: Attempt the Database Repair allowing some data loss by using the DBCC CHECKDB ‘REPAIR_ALLOW_DATA_LOSS’ command:

Use DBCC CHKDB command with Repair Option
Figure 7: Repair Database with DBCC CHECKDB
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

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

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.

free download

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.

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’. It outlined the manual steps to fix the SQL database in suspect mode issue. Also, it explained how you can use Stellar Repair for MS SQL software to repair suspect database.

About The Author

Jyoti Prakash

Problem solver and Data recovery specialist. Usually share informative articles on data recovery, database corruption and ways to recover lost data.  

Best Selling Products

Comments(30)
  1. Ashley Copestick December 21, 2020

    Hi. My SQL Express database was in the Suspect mode. I followed the 9 ‘Steps to Fix the SQL Server Database Suspect Mode Error’ and my problem is fixed.

    So many thanks for that solution!

    Reply
    • Eric Simson December 28, 2020

      Hi Ashley! thank you so much for sharing your feedback.

      Reply
  2. Elan March 9, 2020

    it’s very useful, thank you

    Reply
  3. amzadsyed December 17, 2019

    Thank You, sir, this info is so useful it helped me in retrieving the client DB from suspect mode, Shukran.

    Reply
  4. imran December 11, 2019

    hi,
    if distributed database in suspect state and we set it to emergency then alter database command is not working .then what we need to do?

    Reply
    • Eric Simson December 11, 2019

      You can try Stellar Repair for MS SQL.

      Reply
  5. Aravinda November 25, 2019

    Thanks, it helped me

    Reply
    • Eric Simson November 27, 2019

      Hi Aravinda, Thanks for sharing your feedback.

      Reply
  6. aviram June 4, 2019

    Great Articel!!

    Thanks ths fix Saved me.

    Reply
  7. Abdul Hayee May 8, 2019

    If process deadlock occurs, use this command below to prioritize the process.

    SET DEADLOCK_PRIORITY HIGH ALTER DATABASE dbname SET MULTI_USER;

    Reply
  8. Bruzote March 23, 2019

    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
    • Eric Simson March 25, 2019

      Your inputs will be helpful for the SQL admins because data is critical for everyone.

      Reply
  9. Satish December 5, 2018

    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
    • Eric Simson December 5, 2018

      We request to try a free demo of SQL database repair software and share your findings with us.

      Reply
  10. Amely November 27, 2018

    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
    • Eric Simson November 28, 2018

      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
  11. Lincoln June 27, 2018

    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
    • Eric Simson June 29, 2018

      Good!

      We hope that you can recommend this tool to others.

      Reply
  12. Ron Oz June 4, 2018

    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
    • Priyanka Chauhan June 4, 2018

      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: https://www.stellarinfo.com/blog/sql-database-error-924-database-is-already-open/

      Let me know, if you have further query.

      Reply
  13. Bob April 5, 2018

    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
    • Priyanka Chauhan April 5, 2018

      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
  14. Hari February 21, 2018

    Solution perfectly works fine.

    Reply
    • Jyoti Prakash February 23, 2018

      Hello Hari,

      Thanks for your feedback.

      Reply
  15. chandrapal January 10, 2018

    Yes..!
    Good solution.

    Reply
    • Jyoti Prakash January 19, 2018

      Hello Chandrapal,

      Thanks for your valuable feedback

      Reply
  16. Christopher Cole December 14, 2017

    Does your software support MSSQL 2016?

    Reply
    • Eric Simson December 15, 2017

      Yes!

      You can also verify this feature using free demo copy.

      Reply
  17. Steve September 28, 2017

    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
    • Priyanka Chauhan September 28, 2017

      Dear Steve,

      Thanks for your valuable comment.
      It’s good to know that, our SQL database repair software fixed your issue.

      Keep reading.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.