More Running with Scissors: Corrupting your Database with 823 Errors

This weekend, a question came up on Twitter asking if there was an easy way to simulate an 823 error. It seemed like a fun task to figure out.

In a  previous post, I showed  how to corrupt your database with a Hex editor to cause 824 errors.

What’s an 823 Error?

An 823 error in SQL Server is a severe error that occurs when accessing a database file. It’s described in detail in KB2015755.

This is Useful!

This is useful for learning about corruption, and practicing responses to corruption events.
You can also use this to test configuration scripts you have for database mail, operators, and alerts, to make sure the alerts are working properly.

Disclaimer: these scripts are for test environments only/as always use these at your own risk and be careful not to eat paste.

Step 1: Create a share in Windows on your test machine

First, you need to create a shared folder in Windows.
In order for later steps to work, grant your SQL Server instance’s service account full control over the share.

Step 2: Map a the share to a network drive from the SQL Instance

The ‘Net use’ command is specific to a profile, so the easiest way to handle this is to enable XP_CMDSHELL and map the drive from SQL Server itself.

…A security controversy ensues…

exec sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

EXEC xp_cmdshell 'net use "Y:" "\\MYMACHINEz\_testnetworkDrive" /PERSISTENT:NO'
GO

Step 3: Create a test database with a filegroup, file, and table  on the network drive

Now we just need to create a database, and create a table on the drive.

/* Create test database and add a filegroup and table on the network share */

IF  db_id('TestMe') IS NOT NULL
BEGIN
	USE master;
	ALTER DATABASE TestMe SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE TestMe
END

CREATE DATABASE TestMe
go

ALTER DATABASE TestMe ADD FILEGROUP FG1
ALTER DATABASE TestMe ADD FILE (NAME=f1, FILENAME='Y:f1.ndf', SIZE=128MB)TO FILEGROUP FG1 

USE TestMe
go
CREATE TABLE t1 (
	i INT IDENTITY,
	j CHAR(200) DEFAULT 'x'
) ON FG1
go
INSERT t1 DEFAULT VALUES
GO 20

/* Flush everything to disk */
CHECKPOINT
GO

Step 4: Start a Loop of Reads in Another Connection

Now we want to simulate reads. Open up a new connection against your instance, and run the following commands to repeatedly read data from the t1 table. We use DROPCLEANBUFFERS to make sure we’re reading from disk each time. (We already ran a checkpoint to flush the writes to disk.)

/* Run this in another connection */
SET NOCOUNT ON;
GO
BEGIN
	DBCC DROPCLEANBUFFERS

	SELECT * FROM t1
END
GO 50000

Step 5: Disconnect the network drive, and voila! 823 Error.

Now, back in your first connection, disconnect the network drive with the following command:

EXEC xp_cmdshell 'net use "Y:" /DELETE /Y'

Your connection which is running reads should fail with an error like this:

The connection will automatically be terminated when the error occurs.

Don’t forget to disable XP_CMDSHELL

Like so:

sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

That’s better.

Activity: Recover from the corruption, without bringing the network drive back online

To fully do the activity, you’ll want to add some database backups in before the “corruption” event of disconnecting the network drive. You may want to combine full, differential, and/or log backups, and change data in the table at various points between (and after) backups.

Then, practice bringing things back online. How much data will be lost in each scenario? How quickly can you bring the database online?

Another Solution- The USB Drive

You can also do this by creating the filegroup, file, and table on a USB stick, and removing the USB stick instead of unmapping the network drive.

However, I preferred this example since it’s easy to re-run from management studio itself, and no additional physical devices are required.