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

So Your tempdb is Corrupt

Steve Stedman walks us through how to fix corruption in tempdb: The fact that you know you have corruption in TempDB is good news, that shows that you are running CheckDB against TempDB and many people overlook this.     The corrupt page in TempDB may cause some issues if it is not cleared up.     […]

Read More

Suspect Pages in msdb

Max Vernon explains what the suspect_pages table is in msdb: When SQL Server detects corruption in a database, it reports that corruption immediately to the client who requested the data. But did you know SQL Server also stores the details about which pages have experienced corruption in the msdb database, in the suspect_pages table? Read […]

Read More


November 2018
« Oct Dec »