Creating a Suspect SQL Database

Recently, a customer contacted us asking for the steps to force a SQL Server database into a Suspect state.  Although there are other blog posts about doing exactly that, they didn’t quite work for us.  Let’s take a look at how we got this working for them.

Initial Attempt

To start, we found this guide written by Paul Randal and followed the instructions to create a suspect database.  To save a click, here’s an overview of the process:

1. Create a new database with a single table and some demo rows to use for this purpose
2. Begin a transaction, update a row, and then run CHECKPOINT to force changes to disk
3. Shutdown SQL server (using NOWAIT)
4. Use XVI32 to modify the data or log file and cause corruption
5. Start SQL server back up
6. Check the status of the database

Here’s the matching SQL:

-- Set up the database and sample table
USE [master];
GO
 
CREATE DATABASE [DemoSuspect];
GO
 
USE [DemoSuspect];
GO
 
CREATE TABLE [Employees] (
    [FirstName]   VARCHAR (20),
    [LastName]    VARCHAR (20),
    [YearlyBonus] INT);
GO
 
INSERT INTO [Employees] VALUES ('Paul', 'Randal', 10000);
INSERT INTO [Employees] VALUES ('Kimberly', 'Tripp', 10000);
GO
 
-- Simulate an in-flight transaction
BEGIN TRAN;
UPDATE
    [Employees]
SET
    [YearlyBonus] = 0
WHERE
    [LastName] = 'Tripp';
GO
 
-- Force the update to disk
CHECKPOINT;
GO
 
-- In another query window, run:
SHUTDOWN WITH NOWAIT;
GO
 
-- Use XVI32 to modify the data or log file, then start SQL server back up
 
-- Get the current status of the database:
SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS') AS N'Status';
GO
 
SELECT name, state_desc  FROM sys.databases WHERE name = 'DemoSuspect';
GO

However, we found the state of the database was inconsistent depending on how you checked.  DATABASEPROPERTYEX shows SUSPECT:

Whereas, the sys.databases table and SQL Server Management Studio both show Recovery Pending:

The customer required the sys.databases query to return SUSPECT so this solution didn’t work for them.  For some unknown reason, the database was somewhere between Suspect and Recovery Pending.  Taking a look at the SQL Server log file, we see the following during start up:

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.ldf” may be incorrect. A file activation error occurred. The physical file name ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.ldf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.

trying Again

Seeing these, I had a feeling that 0’ing the first part of the file made it impossible for SQL Server to even attempt recovery.  According to the official doc, Recovery Pending means: “The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting.”.  Based on the errors and the definition of Recovery Pending, it seems that corrupting the first part of the log file made it completely unusable for recovery.  We decided to try the process again, but this time we modified the middle part of the data file instead of the beginning of the log file.  Specifically, I searched for “Kimberly” (which was the name in one of the sample rows entered above) and 0’ed out addresses 1E8088 through 1E80BB in XVI32:

After saving the data file and starting SQL server up, the database is now showing Suspect across the board:

This time during start up, we see more clear data corruption in the log:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x67e30c8b; actual: 0xc3fa3920)…

With this corruption, we got the database into the Suspect state we were looking for.

This was done using SQL Server 2014 but would likely apply to other versions as well.