SQL Server Torn Page DetectionPosted: April 17, 2015
About SQL Server Checksum Page Verification
“when torn page detection is specified, a specific 2-bit pattern is saved and stored on the page header for each of the sixteen 512 byte sectors. This allows SQL Server to detect when a page was not successfully written to disk, but does not check for the correctness of the data stored on the 512 byte sectors.”
Torn page detection
“Disk drive manufacturers generally limit data transfer guarantees to sector boundaries of 512 bytes” !!
“One of the most common problems is when a power failure occurs and a disk drive is in the middle of writing out a database page. If the drive cannot complete the write before it runs out of power (or write operations are cached and there isn’t enough battery backup to flush the drive’s cache) the result could be an incomplete page image on the disk. This can happen because an 8KB database page is actually comprised of 16 contiguous 512-byte disk sectors. An incomplete write could have written some of the sectors from the new page but leave some of the sectors from the previous page image. This situation is called a torn page. ”
How to tell if the IO subsystem is causing corruptions?
“SQLIOSim This is the hardware diagnostic you really want to run. It simulates a very heavy SQL Server workload and should do a far better job of discovering flaws in your hardware setup than individual hardware vendors’ diagnostics will. ”
“You can find info on it at http://support.microsoft.com/default.aspx?scid=kb;en-us;231619 ”
PAGE_VERIFY – Checksum vs. Torn Page Detection
When is a critical IO error not a critical IO error?