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
sp_configure 'xp_cmdshell', 0 RECONFIGURE
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.