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!
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/