Stellar Logo
  • For Individuals
    Back to main menu

    For Individual

    • Windows Data Recovery

      Recovers lost or deleted Office documents, emails, presentations & multimedia files.

      • Free
      • Standard
      • Professional
      • Premium
    • Mac Data Recovery

      Recovers deleted files, photos, videos etc. on Mac.

      • Free
      • Standard
      • Professional
      • Premium
    • Photo Recovery

      Recover photos, videos, & audio files from all cameras and storage on Windows or Mac.

      • Standard
      • Professional
      • Premium
    • iPhone Data Recovery

      Recover deleted photos, videos, contacts, messages etc. directly from iPhone & iPad.

      • Recover
      • Erase
      • Toolkit
    • Video Repair

      Repair multiple corrupt videos in one go. Supports MP4, MOV & other formats.

      • Standard
      • Premium
    • Photo Repair

      Repair multiple corrupt photos in one go. Supports JPEG & other formats.

      • Standard
      • Professional
      • Premium
  • For Business
    Back to main menu

    For Business

    • Email Repair
      • Exchange Repair Repair corrupt EDB file & export mailboxes to Live Exchange or Office 365
      • Outlook PST Repair Repair corrupt PST & recover all mailbox items including deleted emails & contacts
      • OLM Repair Repair Outlook for Mac (OLM) 2011 & 2016 backup files & recover all mailbox items
      • Exchange Toolkit Repair EDB & Exchange backup file to restore mailboxes, convert OST to PST, & convert EDB to PST
      • Active Directory Repair Repair corrupt Active Directory database (Ntds.dit file) & extract all objects in original form
    • Email Converter
      • EDB to PST Convert online & offline EDB file & extract all mailbox items including Public Folders in PST
      • OST to PST Convert inaccessible OST file & extract all mailbox items including deleted emails in PST
      • NSF to PSTConvert IBM Notes NSF file & export all mailbox items including emails & attachments to PST
      • MBOX to PSTConvert MBOX file of Thunderbird, Entourage & other clients, & export mailbox data to PST
      • OLM to PST Convert Outlook for Mac Data File (OLM) & export all mailbox data to PST in original form
      • GroupWise to PST Convert GroupWise mail & export all mailbox items - emails, attachments, etc. - to PST
      • EML to PSTConvert Windows Live Mail (EML) file & export mailbox data - emails, attachments, etc. - to PST
      • Office 365 to PSTConnect to Office 365 account & export mailbox data to PST and various other formats
      • Migrator for Office 365Quickly migrate Outlook data files(OST/PST) directly to Office 365 or Live Exchange
    • File Repair
      • SQL Repair Repair corrupt .mdf & .ndf files and recover all database components in original form
      • Access RepairRepair corrupt .ACCDB and .MDB files & recover all records & objects in a new database
      • QuickBooks RepairRepair corrupt QuickBooks® data file & recover all file components in original form
      • MySQL RepairRepair MyISAM & InnoDB tables and recover all objects - keys, views, tables, triggers, etc.
      • Excel RepairRepair corrupt Excel (.XLS & .XLSX) files and recover tables, charts, chart sheet, etc.
      • BKF RepairRepair corrupt backup (BKF, ZIP, VHDX and .FD) files and restore complete data
      • Database ConverterInterconvert MS SQL, MySQL, SQLite, and SQL Anywhere database files
      • PowerPoint RepairRepair corrupt PPT files and restore tables, header, footer, & charts, etc. like new
      • File Repair ToolkitRepair corrupt Excel, PowerPoint, Word & PDF files & restore data to original form
    • Data Recovery & Erasure
      • Data RecoveryRecover lost or deleted data from HDD, SSD, external USB drive, RAID & more.
      • Tape Data RecoveryRetrives data from all types and capacities of tape drives including LTO 1, LTO 2, LTO 3, & others.
      • Virtual Machine RecoveryRecover documents, multimedia files, and database files from any virtual machine
      • File ErasurePermanently wipe files and folders, and erase traces of apps and Internet activity.
      • Mobile ErasureCertified and permanent data erasure software for iPhones, iPads, & Android devices
      • Drive ErasureCertified and permanent data erasure software for HDD, SSD, & other storage media
    • Toolkit
      • Exchange Toolkit5-in-1 software toolkit to recover Exchange database, convert EDB to PST, convert OST to PST, restore Exchange backup, and reset Windows Server password.
      • Outlook ToolkitComprehensive software suite to repair PST files, merge PST files, eliminate duplicate emails, compact PST files, and recover lost or forgotten Outlook passwords.
      • File Repair ToolkitPowerful file repair utility to fix corrupt Word, PowerPoint, and Excel documents created in Microsoft Office. It also repairs corrupt PDF files and recovers all objects.
      • MS SQL Toolkit5-in-1 software toolkit to repair corrupt SQL database, restore database from corrupt backup, reset database password, analyze SQL logs, & interconvert databases.
      • Data Recovery ToolkitSoftware helps to recovers deleted data from Windows, Mac and Linux storage devices. Also supports recovery from RAIDs & Virtual Drives.
    • Forensic
      • Email ForensicAdvanced email forensic solution for cyber experts to audit, analyze, or investigate emails & gather evidences.
      • Log Analyzer for MySQLAnalyze forensic details of MySQL server database log files such as Redo, General Query, and Binary Log.
      • Exchange AuditorExchange Server monitoring solution to automate audits, scans and generate reports ìn real-time.
      • Log Analyzer for MS SQLTrack & analyze MS SQL Server database transactions log files.
  • Our Partners
  • Lab Services
  • Support
  • About

Trending Searches

Data Recovery

Photo Recovery

Video Repair

iPhone Data Recovery

File Erasure Software

Exchange Repair

OST to PST

PST Repair

Raid Recovery

MS SQL Repair

Database Recovery 6 minute read

How to Recover MS SQL Database from Suspect Mode?

Charanjeet Kaur
Written By
Charanjeet Kaur
Kuljeet Singh
Approved By
Kuljeet Singh
stellar calander
Updated on
March 23rd, 2023

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.

Free Download for Windows

Contents

  • When does SQL database goes to suspect mode?
  • How to get SQL database out of suspect mode?
  • Conclusion

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.

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

Check out the Infographic below for quick solutions to recover database from suspect mode in SQL Server 2008, and higher versions.

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:

  • 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.

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:

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

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.

Check SQL Database Consistency
Figure 5: Check Database Consistency

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
Set SQL Database to Single User Mode
Figure 6: Set Database to Single_User Mode

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)
Use DBCC CHKDB command with Repair Option
Figure 7: Repair Database with DBCC CHECKDB

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

ALTER DATABASE database_name SET MULTI_USER
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 for Windows

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.

Initial Screen of Stellar Repair for MS SQL
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

Trusted by 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.

About The Author

Charanjeet

Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.

Best Selling Products

Stellar Repair for MS SQL

Stellar Repair for MS SQL

Stellar Repair for MS SQL is an enterpri

Read More
Stellar Toolkit for MS SQL

Stellar Toolkit for MS SQL

3-in-1 software package, recommended by

Read More
Stellar Converter for Database

Stellar Converter for Database

Stellar Converter for Database is an eff

Read More
Stellar Repair for Access

Stellar Repair for Access

Powerful tool, widely trusted by users &

Read More

33 comments

  1. Ammar Barya says:
    February 11, 2022 at 5:30 pm

    hi . i am facing this issue at my DR site . PR is showing the DB is in Synchronized but DR is showing initializing/suspect . please guide me how to do it?

    Reply
  2. Ammar Barya says:
    February 11, 2022 at 5:27 pm

    i have this issue at my DR site . i restored the DB at DR and when i add that DB in high availability group it should that command executed successfully but then at DR status of that DB changed to initializing/suspect. can any one help me in this? i am quite new at SQL server

    Reply
  3. Ammar Barya says:
    February 11, 2022 at 5:26 pm

    i have this issue at my DR site . i restored the DB at DR and when i add that DB in high availability group it should that command executed successfully but then at DR status of that DB changed to initializing/suspend. can any one help me in this? i am quite new at SQL server

    Reply
  4. Ashley Copestick says:
    December 21, 2020 at 1:43 pm

    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
    1. Eric Simson says:
      December 28, 2020 at 8:55 am

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

      Reply
  5. Elan says:
    March 9, 2020 at 4:53 am

    it’s very useful, thank you

    Reply
  6. amzadsyed says:
    December 17, 2019 at 1:16 pm

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

    Reply
  7. imran says:
    December 11, 2019 at 11:06 am

    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
    1. Eric Simson says:
      December 11, 2019 at 11:46 am

      You can try Stellar Repair for MS SQL.

      Reply
  8. Aravinda says:
    November 25, 2019 at 6:54 am

    Thanks, it helped me

    Reply
    1. Eric Simson says:
      November 27, 2019 at 4:05 am

      Hi Aravinda, Thanks for sharing your feedback.

      Reply
  9. aviram says:
    June 4, 2019 at 5:53 am

    Great Articel!!

    Thanks ths fix Saved me.

    Reply
  10. Abdul Hayee says:
    May 8, 2019 at 11:11 pm

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

    SET DEADLOCK_PRIORITY HIGH ALTER DATABASE dbname SET MULTI_USER;

    Reply

Comments navigation

Older comments Page 1 Page 2

Leave a comment Cancel reply

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

Image Captcha
Refresh Image Captcha

Enter Captcha Here :

Table of Contents    arrow

  1. When does SQL database goes to suspect mode?
  2. How to get SQL database out of suspect mode?
  3. Conclusion

Categories

Related Posts

related post
Database Recovery

[Fixed]: Database cannot be Opened. It is in the Middle of a Restore

Stellar Author Bharat Bhushan February 6, 2023 Read More
related post
Database Recovery

How to Fix SQL Attach Database Error 9004?

Stellar Author Bharat Bhushan February 3, 2023 Read More
related post
Database Recovery

How to Rebuild and Restore Master Database in SQL Server?

Stellar Author Bharat Bhushan February 2, 2023 Read More

Free Trial for 60 Days

Technology You Can Trust A Brand Present Across The Globe

  • tuv1
  • tuv2
  • Nist
  • hipa

Stellar Official Website

Stellar Data Recovery Inc.
48 Bridge Street Metuchen,
New Jersey 08840,
United States

ALSO AVAILABLE AT

ALSO AVAILABLE AT

About

  • About us
  • Career
  • ISMS Policy
  • Privacy Policy
  • Terms of Use
  • License Policy
  • Refund Policy
  • End User License Agreement

RESOURCES

  • Blog
  • Articles
  • Product Videos
  • Knowledge Base
  • Case Studies
  • Whitepapers
  • Software Catalog

NEWS & EVENTS

  • News
  • Events

PARTNERS

  • Affiliates
  • Resellers
  • Distributors

Useful Links

  • Contact Us
  • Support
  • Special Offers
  • Student Discounts
  • Awards & Reviews
  • Downloads
  • Store
  • Sitemap

Stellar & Stellar Data Recovery are Registered Trademarks of Stellar Information Technology Pvt. Ltd. © Copyright 2023 Stellar Information Technology Pvt. Ltd. All Trademarks Acknowledged.

Follow Us Facebook Twitter Linkedin Youtube

  • Notron
  • dcma

Subscribe to our newsletter!

Its good time to subscribe and get the latest promtion

With subscribing you agree with our Terms & Conditions

This website uses cookies in order to provide you with the best possible experience and to monitor and improve the performance of the site in accordance with our cookie policy. You can reject cookies at any time in your browser settings.

cookies-cross
Got it