How to Create a Suspect Database???….

Yes, You heard it right! How to Create a SUSPECT Database. You might be thinking, why would someone make a Database Suspect! I got you! Well, This post is not to teach you how to make your Production Database as “SUSPECT” 😉  I thought of sharing this Info, to fight a PANIC situation where you see a SUSPECT Database in your environment.  Create a SUSPECT Database in your play area and practice how to recover from that situation, get familiarize with a SUSPECT DB and play with it and try to recover from it far before a real Disaster hits your Prod Database(I hope not).

In this post, I’m not going to show how to recover from “SUSPECT DB”, Instead just to simulate a scenario where your DB goes into SUSPECT State. Let’s begin!!!…

Couple of months ago, I’ve written a small blog post(see here) on how to simulate 824 Error using Hex Editor. I’ll be using the same tool to achieve our goal here in this post.

Simple Method:

Step 1: Create a Dummy Database to play with.

Step 2 : Shutdown your SQL Server DB Engine Service.

Step 3: Open your LDF file with Hex Editor and edit it manually with few Bogus entries.

Step 4: Start your SQL Instance, you should be seeing your Database marked as Suspect by SQL Server.

Alternate, more practical method:

Step 1: I’ve created a dummy database to play with “Suspect_tst”.

Step2: I’ve started an explicit transaction(but didn’t committed or Rolled Back) and left it as an Open transaction and did a Checkpoint.

Step3: Force Shut down your SQL Server.

Step4: Open your ldf file in Hex Editor and choose a random row and fill them with some random bogus values same as in method 1.

Step5: Start your SQL Server, You will be seeing a Suspect Database 🙂

You can see the below Screenshot what i meant in Steps 1 and 2.

Now, as of Step 3, all you’ve to do is just open a new query and type “SHUTDOWN  WITH NOWAIT;“(Never ever run this on Production, Trust me, you’ll immediately loose your job:D)

for Step 5, as you can see in the below screenshot, I’ve opened ldf file and filled in 2nd Row with all 1’s.

Save and now Start your SQL Server Instance! Once SQL has been started, query your DB Status as shown below.

Tadaaaaa! We did it….:)

Now, you know how to simulate this most disastrous situation, play with this database for recovery, loosing as less amount data as you can. Cheers!

One thought on “How to Create a Suspect Database???….

  1. Thanks for sharing this information. I would like to add my personal experience on this issue. When I faced the suspect mode issue, I have done the following 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

    Refresh the database server and verify the connectivity of database

    I followed the steps mentioned in this blog: https://www.stellarinfo.com/blog/recover-sql-database-from-suspect-mode/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.