Which Sector is "torn" in Torn Page Error ?

Recently I'm doing the analysis of the "torn page" thing again. If the SQL server throws the torn page error, it doesn't show which sector the page is torn. Let's review again the definition of "torn page" from Kalen Delaney's "Inside Microsoft SQL Server 2000" :

..it causes a bit to be flipped for each 512 byte sector in a database page (8 KB) whenever the page is written to disk..

In practice, there are two kinds of torn page error events. By this event, I am referring to the behaviour of MS SQL 2000.

First, torn page error during the database attachment process. Usually, this is caused by some corrupted data that occur in MS SQL server's important pages, such as PFS pages.

Second, torn page during accessing records, i.e. ours important pages :)

Since the data is of utmost important, the precise determination of which sector of size 512 bytes that gets torn apart is required.

To perform this kind of analysis, I am creating the testing database (named Test), create table and input some record using SQL Enterprise Manager. Next, I am determining the first page that contain my data :

use test
dbcc checkalloc

***************************************************************
Table TestTbl Object ID 357576312.
Index ID 0. FirstIAM (1:91). Root (1:90). Dpages 1.
Index ID 0. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************


So, the first page of my data is located at page 90 which is at offset 90 * 8192 = 737280 = 0xB4000 in physical file.

Now, lets see the physical page created by MS SQL Server using hex editor :




Here, the offset 5 (marked with green box) in the page header is somehow related to the torn page checking. Turning the first nibble to zero (i.e. from 0x81 to 0x80) should bypass the torn page checking. But it is not, see the paragraph below for further information.

OK, let's go back to the original question. To determine which sector is get torn, we have to examine the calculation of m_tornbits flag in relation to the last offset at each 512 sector. Here is how it is done :

First byte of m_tornbits is masked with 0x03, so any byte will end up with either the value 0x00, 0x01, 0x02 or 0x03. For the above example, first byte is 0x01, with this operation, it will give 0x01. This value is used to compare with the last byte offset of second sector onward (or sector 1, if I count first sector as zero) (starting from offset 0x03FF onward).

The last byte of each sector is performed using the same operation as above and compared with the value from masked first byte of m_tornbits. If there are any differences, the torn page error will be thrown.

So, using the above operation, it is possible to determine which sector is not in synch with the rest of the sector or in other word, which sector is a torn page. But, it is inevitable with the assumption that, the first sector which contains the page header, hence the m_tornbits value, is a valid one.

However, it is not possible to temporarily turn off torn page checking by resetting the byte in offset 5 at page header. SQL server seems perform the consistency checking :



So, you should change the last offset of defected sector one by one in order to be accessed by MS SQL Server.

Happy torn page-ing :)