When was Torn Page Detection and Checksum introduced to SQL Server and what are the upgrade behaviors?

Question

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.

Answer 1

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 :-)

Answer 2

Take a look at the reference from BOL:

When a user or system database is upgraded to SQL Server 2005 or a later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. We recommend that you use CHECKSUM

This dictates that prior to SQL Server 2005 the option for TORN_PAGE_DETECTION existed, but not CHECKSUM.

And to answer your second point:

... and that upgrading or restoring a DB from a prior version would maintain it's previous page verify method.

Yes that is correct. You would need to explicitly set the database to utilize the CHECKSUM page verification method.

Answer 3

There are two different options in modern SQL Server for page verify

There are three as you stated: TORN_PAGE_DETECTION, CHECKSUM, and NONE.

I believe CHECKSUM was introduced in SQL Server 2005

As quoted from this MSDN article titled "Buffer Management": Torn page detection was introduced in SQL Server 2000. Checksum was introduced in SQL Server 2005.

A synopsis of other things noted in this article is that the page verify mechanism is specified at database creation time. So it depends on who and how they created the database as to what it is set to, could also be controlled by what model database is configured to. Also interesting to note is that if you change the setting it does not take affect over the whole database, only when the page is written to next. As well according to Paul Randal it is only done when the page is read into memory, changed, and then written back to disk; that info is here.

I have a production database that went into production using SQL Server 2000, though may have been developed against SQL Server 7.0, and has since moved to a SQL Server 2008 R2 server. Page Verify is set to NONE though I expected it to be TORN PAGE DETECTION.

Anyone that has permissions to the database instance can modify that value. It could have persisted through upgrades as stated on MSDN here:

When a user or system database is upgraded to SQL Server 2005 or a later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained

It could have also been modified at a later time because someone misunderstand the configuration and was shooting in the dark to try and solve a problem.

I was wondering when TORN PAGE DETECTION became a Page Verify feature

SQL Server 2000 as stated above.

how it behaves when migrated or upgraded to newer editions.

The previous setting is retained during upgrade as stated above.

Now I would like to point out the fact that other links provided by folks state that SQL Server 7.0 is when torn page detection was available. Which as stated in those articles is true, however it is proven many times over that Microsoft documentation should not be held as truth in all circumstances. There are many where they are wrong. So with that said how can you determine which answer is acceptable? We all provided documentation by Microsoft to support our answer.

As well note that torn page detection is on the depreciation list as of SQL Server 2012, so what is the concern with how it was set on your databases to begin with. If I saw it set to anything other than CHECKSUM I immediately change it and move on to other more important task. I have no concern on how a bad configuration was put in place it is more important to correct it and then ensure those who have permissions to change it are informed of why that configuration item should not be changed to anything else. Just my $0.02

Answer 4

As both @Thomas Stringer and @Kin said it is introduced in SQL Server 2005 and I believe it works in all editions of SQL Server. For TempDB though CHECKSUM was introduced in SQL Server 2008

Link