TORN_PAGE_DETECTION Internals (SQL 2005 onwards)

CHECKSUM is in the majority of cases the more appropriate PAGE_VERIFY option as it provides a much more complete check (slightly higher overhead) when compared to TORN_PAGE_DETECTION.

I thought today however I would write a simple post to show how the internals of TORN_PAGE_DETECTION work even tho it is less used. I am guessing that you have read, and know that torn page detection is used to check for torn pages… You also know that SQL Server internal structures are centered around an 8K page or 8192 Bytes. This can be thought of as 16 * 512 Bytes (Sectors) from 0 to 15 in my representations.

When Torn Page Detection has been selected for a database SQL Server generates a 4 byte value for each Page when it is written to disk. This is stored from Byte 60-63 in the Page Header.

This 4 byte value is calculated by taking the last 2 bits from each 512 byte sector, for sector 01 to sector 15. The value for Sector 00 which contains the Page Header alternates between 00 and 10. Incomplete IO can be checked by comparing the stored 2 bit values in m_tornBits with the last 2 bits for each sector.

If bytes 60-63 were stored as 02 3D 02 18 then converting 18023D02 to binary would result in 00 01 10 00 00 00 00 10 00 11 11 01 00 00 00 10. (Left to right would read the last 2 bits from the Byte 8191 down to Byte 1023. The final byte being either 10 or 01)

Byte Position Hex Bin Last2Bits
Sector 01 Byte 1023 10 10000 00
Sector 02 Byte 1535 00 0 00
Sector 03 Byte 2047 00 0 00
Sector 04 Byte 2559 01 1 01
Sector 05 Byte 3071 83 10000011 11
Sector 06 Byte 3583 D3 11010011 11
Sector 07 Byte 4095 00 0 00
Sector 08 Byte 4607 06 110 10
Sector 09 Byte 5119 00 0 10
Sector 10 Byte 5631 10 10000 00
Sector 11 Byte 6143 00 0 00
Sector 12 Byte 6655 00 0 00
Sector 13 Byte 7167 12 10010 10
Sector 14 Byte 7679 09 1001 01
Sector 15 Byte 8191 00 0 00

I was going to finish this post with a list of potential issues with torn page detection, however if you have followed the description of how it works then you should have a good idea. Plus you are more than likely using CHECKSUM anyway.