How to Recover SQL Server database from Suspect mode?

Summary: This post is for user who is looking for solution to recover SQL Server Database from Suspect Mode. This post describes steps to recover database from suspect mode by using T-SQL Commands in SSMS. Also, it describes other ways to recover using recovery tools.

What is Suspect Mode?

In simple terms : Database goes into Suspect Mode when When SQL Server tries to initiate database and fails because of certain reasons. Mostly it can be System failure or disk issues or file inaccessible.

Here are detailed reasons when and how it can happen.

What causes databases go into Suspect Mode?

I have multiple case scenarios when user was using the database or application on the SQL database which was running perfectly fine, and all of sudden in the next morning database goes into suspect mode.

In such case I ask few questions in this sequence and user finds the cause of issue in my questioner.

Here are few reasons when you may find database in Suspect mode.

System Failure : System or Windows failed or corrupted accidently

Improper System Shutdown : Windows did not shutdown properly last time.

Low Disk Space : Low disk space on the machine, which is not allowing access mdf / ldf files.

Missing Log File : This may happen when Log file is not available

Files inaccessible : Inaccessibility of MDF or LDF file set it to Suspect Mode

Database File Corrupt: There are many reasons of corruption of database file. In few cases of database corruptions given solution below is helpful. In other cases you have to find out what is cause of corruption of database file.

How to Recover using SQL Command?

Step 1: Set database to Emergency Mode

Start SQL Server Management Studio(SSMS) or SQLCMD tool (if you do not have SSMS) and Open New Query window

Turn off Suspect flag using this command

EXEC sp_resetstatus 'yourdbname';

Set database to Emergency

ALTER DATABASE yourdbname SET EMERGENCY

Step 2: Run Consistency Check using DBCC

DBCC CHECKDB ('yourdbname')

Step 3: Bring database into Single User mode

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 4: Backup database for safe side of data loss

Step 5: Execute database repair with allow data loss

DBCC CHECKDB ('yourdbname', REPAIR_ALLOW_DATA_LOSS)

Step 6: Set database to Multi User

ALTER DATABASE yourdbname SET MULTI_USER

Lets collect all the steps into one query block and this is how all steps look like.

--Set Database to Emergency
ALTER DATABASE yourdbname SET EMERGENCY;

-- Check database consistancy
DBCC CHECKDB (yourdbname );
-- Set user to Single User
ALTER DATABASE yourdbname  SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Rapair database with Allow Data Loss
DBCC CHECKDB (yourdbname , REPAIR_ALLOW_DATA_LOSS)
-- Set database to multi User
ALTER DATABASE yourdbname  SET MULTI_USER

Recover using Recovery Tools

In most of cases, use gets success recovering database is into normal condition. However, you may not be able to recover database from Suspect Mode to Normal mode if database files are badly corrupted. In such situation we can help from the specially designed Database Recovery Tools like Stellar and EaseUs MS Recovery Tool.

I have used Stellar Database Recovery Software many times to recover database from critical condition.

Conclusion

Suspect mode is not a dangerous status of database but a status which could not resolved by SQL Server itself. We also understood that it can be resolved by running bunch of commands.


See Also :

How to recover database from Recovery Pending status

Why we should not Shrink SQL Server database file?

9 thoughts on “How to Recover SQL Server database from Suspect mode?

  • November 30, 2020 at 4:19 pm
    Permalink

    These are in fact fantastic ideas in about blogging. Shawnee Tracey Kipton

    Reply
  • December 8, 2020 at 7:01 am
    Permalink

    I think this is a real great blog post. Really looking forward to read more. Really Great. Brianne Augy Blockus

    Reply
  • December 8, 2020 at 10:12 pm
    Permalink

    There is visibly a bundle to realize about this. I suppose you made some good points in features also. Caren Ulysses Bobbette

    Reply
  • December 9, 2020 at 4:48 am
    Permalink

    Great, thanks for sharing this blog article. Cool. Cinderella Nickey Zoeller

    Reply
  • December 9, 2020 at 6:21 am
    Permalink

    This is a same weighty post. Thanks instead of posting this. Reina Alard Thurston

    Reply
  • December 9, 2020 at 11:14 am
    Permalink

    Way cool! Some very valid points! I appreciate you writing this article and the rest of the site is really good. Cyndy Errick Turne

    Reply
  • December 9, 2020 at 12:50 pm
    Permalink

    Please take a look at the web-sites we adhere to, including this a single, because it represents our picks in the web. Deeann Brooke Lonee

    Reply
  • December 9, 2020 at 3:18 pm
    Permalink

    Pretty! This has been an extremely wonderful article. Thank you for providing these details. Leonore Maison Gelasius

    Reply
  • December 10, 2020 at 12:58 am
    Permalink

    Excellent post! We will be linking to this great content on our site. Keep up the great writing. Shirlee Bent Pagas

    Reply

Leave a Reply

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