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 June 26, 2020 Database Recovery 29 Comments |   5 min read

Summary: This post is intended for users looking for solutions to recover MS SQL database (db) from Suspect Mode. It describes steps 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

Reasons Why SQL Database is Marked as Suspect

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.

Steps to Recover MS SQL Database from Suspect Mode

NOTE: You can try restoring the suspect database 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

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

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

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

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

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 database in suspect mode and several others. The software uses advanced algorithms to repair SQL db and restores it to normal state (online).

Steps to Recover ‘SQL Database from Suspect Mode’ with 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 Select Database window, choose Browse or Search to select SQL database file (.mdf) of the suspect database.

Select corrupt database .mdf file

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 database in suspect mode issue. Also, it explained how you can use Stellar Repair for MS SQL software to repair and restore the 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(29)
    « Older Comments
  1. Elan March 9, 2020

    it’s very useful, thank you

    Reply
  2. 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
  3. 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
  4. Aravinda November 25, 2019

    Thanks, it helped me

    Reply
    • Eric Simson November 27, 2019

      Hi Aravinda, Thanks for sharing your feedback.

      Reply
  5. aviram June 4, 2019

    Great Articel!!

    Thanks ths fix Saved me.

    Reply
  6. 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
  7. 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
    « Older Comments

Leave a Reply

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

Time limit is exhausted. Please reload CAPTCHA.