Torn page detection vs Page Checksum

TornPage Detection:

Up to sql server 2000, for data corruption process is called torn page detection, this option is default on. The data page is of size 8k and  combination of 8 pages is called extent. where as hardware storage is stored in tracks and sectors,sector size is 512 bytes. and page header contains information of 512 bytes. so whenever data is updated /changes it save in a chuck of 512 bytes. if this option is enabled, it validates for 512 bytes on page header. and if any data corruption occurs it will check in 512 bytes chucks. so it is not accurate and generally this occurs 823, 824 error. most of the time the only option left would be to restore the last good backup.

Page Checksum: in sql server 2005. development team has introduce a new improved option over torn page detection called page checksum. whenever page is read from physical storage to memory or write to physical storage from Memory. on every page there will be a bitmap which is sum the bits on that page and validate it. this process is very efficient. So compare to torn page of 512 bytes it checks for every bit on page. also in for sql server 2005 onwords page level restore page is also possible.

 Checksum is also included for backup and restore.

 http://technet.microsoft.com/en-us/library/cc966500.aspx
http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/6d45e4a9-2c9b-451d-b7c2-1fa96c6cd03a
http://www.sqlservercentral.com/Forums/Topic663009-266-1.aspx#bm663026

Please correct me if I am missing something.

Thanx

Vinay

About these ads
This entry was posted in Basic, Computers and Internet, What I learned today and tagged , , . Bookmark the permalink.

2 Responses to Torn page detection vs Page Checksum

  1. Jude says:

    Just wanted to add something to para1, what ever the error (i.e 823,824), i suggest that checkdb should be executed and is interpreted before attempting a restore. It can just be a integrity issue with the index page that has caused the error which does not require a DB restore.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s