Sql-server – When was Torn Page Detection and Checksum introduced to SQL Server and what are the upgrade behaviors

sql server

There are two different options in modern SQL Server for page verify; being Torn Page Detection and Checksum. None is also of course an option.

I believe Checksum was introduced in SQL Server 2005 and that upgrading or restoring a DB from a prior version would maintain its previous page verify method. i.e. there was no implicit upgrade.

The problem involved is that we have a production database that went into production using SQL Server 2000 and has since moved to a SQL Server 2008 R2 server. Page Verify is set to None when I had been expecting it to be Torn Page Detection. Going back this amount of time we seem to think the DB was originally developed in SQL Server 7.0 then migrated to SQL Server 2000 and this may explain the observed result.

I was wondering when Torn Page Detection and Checksum became a feature of SQL Server, and how they behaved when migrated or upgraded to newer versions.

Edit: Summing up some of the answers:

There is a little discrpenacy over some of the dates for when Torn Page Detection came into SQL Server.

Link 1: http://support.microsoft.com/kb/230785

Link 2: http://technet.microsoft.com/en-us/library/aa337525(v=sql.90).aspx

The first link indicates SQL 7.0 and the second SQL2000. I tend to put my faith in the SQL7.0 suggestion and that link two was confused over it being off by default in SQL7.0 and on by default in SQL2000.

Best Answer

In SQL Server 2000, if you want to identify corrupt pages, then the database option TORN_PAGE_DETECTION should be set to TRUE.

But in SQL 2005 and up, a new setting PAGE_VERIFY replaced the old TORN_PAGE_DETECTION which allows to choose from two different types of page verification : TORN_PAGE_DETECTION and CHECKSUM.

Now the question comes which one to set - TORN_PAGE_DETECTION or CHECKSUM ?

TORN_PAGE_DETECTION - writes a bit for every 512 bytes in a page allowing you to detect when a page was not successfully written to disk. The catch is that it wont tell you if the data stored in those 512 byes is actually correct or not due to the fact that couple of bytes may have been written incorrectly.

CHECKSUM - will caluclate a checksum of the page both when a page is written and when a page is read, assuming it has checksum on it.

The SQL Server computes the checksum based on the bit pattern on the page, stores it in the page header and then issues an I/O to write the page. When the SQL Server reads the page, it re-computes the checksum using the same logic and then compares it with the value available in the page header. If the checksum value matches then it is assumes the page did not get corrupted during the write-read cycle.

Since the cost of computing the checksum is incurred on each page read and write, it can add to the CPU overhead and can possibly impact the throughput of your workload. Another thing to keep in mind is that the checksum is not unique for a specific bit pattern on the page. Two pages can possibly map to the same checksum value. So there is remote possibility that page corruption may go undetected.

Reference : Checksum in SQL2005

To specifically answer your questions :

I believe Checksum was introduced in SQL2005 and that upgrading or restoring a DB from a prior version would maintain it's previous page verify method. i.e. there was no implicit upgrade.

Yes CHECKSUM was introduced in SQL Server 2005 and is the DEFAULT. When you upgrade from 2000 to 2005, you have to explicitly change the database option Page Verify to use CHECKSUM.

If you restore the database already created on sql 2005 to another server running sql 2005, you dont have to set it. It will persist to what ever you have set the Page Verify option to.

I've not succeeded in researching when Torn Page Detection came in

From: http://support.microsoft.com/kb/230785

Versions of SQL Server earlier than 7.0

Versions of SQL Server earlier than 7.0 did not provide log parity or torn bit detection facilities. In fact, those versions can write the same log page multiple times until the log records fill the 2-KB log page. This can expose transactions that have successfully committed. If the log page is being rewritten during a failure, a sector with the committed transaction may not get rewritten properly.

Thus, TORN_PAGE_DETECTION has been around since SQL Server 7.0. Even then, the default was that it was not enabled (same link).

Note Torn page detection is not enabled by default in SQL Server 7.0. See sp_dboption for how to enable the detection on your system.

Therefore, if the database was developed against a 7.0 instance and was subsequently upgraded, it would have upgraded the with the extant PAGE VERIFY option of NONE (as @ThomasStringer noted in his answer).

Edit : 09/24/2013 To improve the answer :

Refering to my SQL Server Internal notes from SQLSkills, I found that using a page dump, you can verify if torn bit detection - TORN_PAGE_DETECTION or CHECKSUM was enabled or not :

use database_name -- change here for your database !!
checkpoint
go 
dbcc traceon (3604)   -- send output to screen
go
dbcc page (dbaalert, 1,1,0)
dbcc traceoff (3604)  -- turn off the trace flag
go

m_tornBits : This holds either the page checksum or the bits that were displaced by the torn-page protection bits – depending on what form of page protection is turnde on for the database.

Note: I dont have any older sql server versions running. Below is confirmed from sql server 2000 and up. If you have a 7.0 or 6.5 running around, you can confirm it as well :-)

enter image description here

Related Question