Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Algorithm of Checksum and Torn Page Detection [Snippet]

DZone's Guide to

Algorithm of Checksum and Torn Page Detection [Snippet]

Let's take a look at an algorithm of Checksum and Torn Page Detection.

· Database Zone ·
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

SQL server data page has three kinds of PAGE_VERIFY options since SQL Server 2005: NONE, Torn Page Detection, Checksum.

There is no algorithm of PAGE_VERIFY for NONE option.

For Torn Page Detection, each disk sector has 512 bytes, a SQL data page is 8KB occupied 16 disk sectors, so one SQL data page can be divided into 16 blocks of 512 bytes. Torn Page Detection gets the last two bits of the last byte of each block and stores them in the page header using 4 bytes. Torn Page Detection replaces the last two bits of the last byte of each block with either 01 or 02 and saves the page to the file. With Torn Page Detection, 16 blocks of a page always have the same last two bits, either 01 or 02. Torn Page Detection only checks the last two bits of each block.

By default, NTFS file system allocated unit size is 4KB, one page may not be saved to disk at the same time. So we often find torn bits of the first 8 blocks is different from the last 8 blocks due to I/O error.

For Checksum, it uses bitwise operation "xor" for every 4 bytes in one page, except the 4bytes begins from page offset 003C. Bytes may shift a little bit decided by its offset, and the result of  "xor" operation is stored as 4 bytes begin from 003C in the page header. So, if you modify one bit of the page by hand (such as using winhex to modify one bit of a byte), you will get a Checksum PAGE_VERIFY error. while Torn Page Detection does not in most cases.

Here is my Delphi function to check a Torn Page Detection or Checksum page is valid:

function f_isvalidpage(apage :array of char) :boolean;
var ac  :array[0..3] of char;
var bbit :byte;
var i,isum,itmp,isum0,j :integer;
begin
    result :=false;
    if ((byte(apage[5]) and $0F) = 1)  then //torn page
    begin
      bbit :=byte(apage[60]) and 3;
      if ((byte(apage[1023]) and 3 ) <>bbit) or  ((byte(apage[1535]) and 3 ) <>bbit) or ((byte(apage[2047]) and 3 ) <>bbit) or ((byte(apage[2559]) and 3 ) <>bbit) or ((byte(apage[3071]) and 3 ) <>bbit) or ((byte(apage[3583]) and 3 ) <>bbit)  or ((byte(apage[4095]) and 3 ) <>bbit) or
       ((byte(apage[4607]) and 3 ) <>bbit) or  ((byte(apage[5119]) and 3 ) <>bbit) or ((byte(apage[5631]) and 3 ) <>bbit) or ((byte(apage[6143]) and 3 ) <>bbit) or ((byte(apage[6655]) and 3 ) <>bbit) or ((byte(apage[7167]) and 3 ) <>bbit)  or ((byte(apage[7679]) and 3 ) <>bbit) or ((byte(apage[8191]) and 3 ) <>bbit) then
      begin
        result :=false;
        exit;
      end
      else
      begin
        result :=true;
        exit;
      end;
    end;

    if ((byte(apage[5]) and $0F) = 2)  then //checksum page
    begin

       ac[0] := apage[60];
       ac[1] := apage[61];
       ac[2] := apage[62];
       ac[3] := apage[63];
       move(ac,isum0,4);


       j :=1  ;
       isum := 0;


      for i :=0 to 2047  do
      begin

       if i =15 then continue;
       if i<128 then j :=15;
       if (i>= 128) and (i<256)  then j :=14;
       if (i>= 256) and (i<384)  then j :=13;
       if (i>= 384) and (i<512)  then j :=12;
       if (i>= 512) and (i<640)  then j :=11;
       if (i>= 640) and (i<768)  then j :=10;
       if (i>= 768) and (i<896)  then j :=9;
       if (i>= 896) and (i<1024)  then j :=8;
       if (i>= 1024) and (i<1152)  then j :=7;
       if (i>= 1152) and (i<1280)  then j :=6;
       if (i>= 1280) and (i<1408)  then j :=5;
       if (i>= 1408) and (i<1536)  then j :=4;
       if (i>= 1536) and (i<1664)  then j :=3;
       if (i>= 1664) and (i<1792)  then j :=2;
       if (i>= 1792) and (i<1920)  then j :=1;
       if (i>= 1920) and (i<2048)  then j :=0;



       ac[0] := apage[i*4];
       ac[1] := apage[i*4+1];
       ac[2] := apage[i*4+2];
       ac[3] := apage[i*4+3];
       move(ac,itmp,4);
       itmp := (itmp shl j) or (itmp shr (32-j))    ;
       isum := isum xor itmp;


      end;

      if isum <> isum0 then  
      begin
        result :=false;
        exit;
      end
      else
      begin
        result :=true;
        exit;
      end;

    end;


end;

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
sql server ,sql database recovery ,sql database repair ,sql server 2008

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}