Corrupting Databases For Fun And Profit

Eric Blinn has started a new series on database corruption.  In part one, he shows us how to corrupt a database (probably by letting it listen to Alice Cooper and Frank Zappa):

I’m going to start a series of posts regarding corruption detection, prevention, and correction.  In order to detect corruption we must first have a corrupt database.  This post is going to show how to purposely corrupt a database.  I’ll refer back to this post often as it will be the basis of most of the future posts in this series which will pick up where this leaves off.

Step 1.  Create a new database.  You didn’t think I was going to ask you to corrupt a real database did you?  We will create one table with some names in it.  The table is abnormally wide on purpose.  The goal is to get fewer rows on any one page of data so that we can get several pages of data without needing many hundreds of rows.

Part two explains the concept of page verification:

Page verification is a process in SQL Server where the engine writes extra data to the header of the page while writing it to disk.  This extra data can be used to verify that the data later read from that page is what was expected.  There are 3 options for the setting.  They are NONE, CHECKSUM, and TORN_PAGE_DETECTION.  The torn page option is deprecated.  It should not be used and will not be covered in this series.

When set to CHECKSUM SQL Server will quickly determine a checksum for the page of data and write it to the header of the data page.  Any time from that point forward when the page is read from disk SQL Server will perform the same checksum calculation and compare that to to the stored value in the header of the page.  If the value matches that would indicate there is probably not corruption. If the values do not match that would indicate there is almost certainly some kind of corruption.

Corruption is one of the scariest things that can happen to a database; knowing how to respond to an incident of corruption is critical even as the incidence of corruption is quite low in practice (unless you’re running on awful hardware).

Related Posts

Backups With Checksum Validation

Eric Blinn shows the upside to performing checksum validation during backups: Since a full backup reads every data page it makes sense that we can ask SQL Server to calculate and compare each checksum during this operation. Even a differential backup reads all of the pages for any extent (a group of 8 data pages) […]

Read More

PFS Corruption When Moving From SQL Server 2014

Paul Randal notes a bug in SQL Server 2014: I’m seeing reports from a few people of DBCC CHECKDB reporting PFS corruption after an upgrade from SQL Server 2014 to SQL Server 2016 or later. The symptoms are that you run DBCC CHECKDB after the upgrade and get output similar to this: Msg 8948, Level 16, State 6, Line […]

Read More


November 2018
« Oct Dec »