Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
it was introduced when the code was committed. –  swasheck Sep 18 '13 at 14:58
    
Why does it matter? What's the problem being solved here? –  Marian Sep 18 '13 at 15:01
    
@swasheck - sorry I do not understand your comment. –  Paul Sep 18 '13 at 15:26
1  
@Paul voted to reopen –  swasheck Sep 18 '13 at 16:56
1  
@Paul I have added dbcc page info to check for torn page or checksum bits in my answer. –  Kin Sep 24 '13 at 21:35

4 Answers 4

up vote 11 down vote accepted

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

share|improve this answer
    
@Kin aye I know it was around in SQL2000 also, would like to know when it was first introduced. by the phrase "moving to versions prior" let us pretend TPD was introduced in SQL2000 then moving from SQL7 to SQL2000 would be moving between versions prior to SQL2005. I am interested to know if TPD was toggled on during such migrations. I fully expect it would not but have not been able to verify such. –  Paul Sep 18 '13 at 15:28
    
@paul I deleted them because I felt that my edit encompassed the comments –  swasheck Sep 24 '13 at 23:43
    
@Kin I tried your DBCC code on SQL2008R2 and got a m_tornbits values of 1711843878.. so it's a measure rather than an boolean would you say? –  Paul Sep 26 '13 at 11:12
    
@Paul it means that checksum or torm page is ON. On a2005 and up, you should go for Only CHECKSUM. Wondering if you have any 7.0 lying around to test ? –  Kin Sep 26 '13 at 12:54

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.

share|improve this answer
    
Thanks for the reference @Thomas but that doesn't answer when TORN PAGE DETECTION first became available in SQL Server. –  Paul Sep 18 '13 at 14:05
2  
@Paul This does answer that torn page detection existed prior to SQL Server 2005. Are you looking for what version of SQL Server that page verification came into play? Besides a history lesson, I'm not sure what you're looking to gain there. What problem exactly are you trying to solve? –  Thomas Stringer Sep 18 '13 at 14:24
    
I was seeking to know when it came into existence and how it behaved during migrations. I'm hoping to understand how some of our very old DBs have come to have the settings they do on some of our modern (ish, SQL2008R2) servers. –  Paul Sep 18 '13 at 15:41
    
If your databases have TORN_PAGE_DETECTION, then that could surely result in an upgrade from pre-SQL Server 2005 and that page verify option has persisted. –  Thomas Stringer Sep 18 '13 at 15:58
    
they do not have TPD enabled, that was the baffling part. Other answers have provided the solution to the issue now (SQL7.0 had TPD, but not enabled by default and this was the version originally developed against) –  Paul Sep 26 '13 at 11:14

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

share|improve this answer
    
I think that 2000 was when TPD defaulted to ON. As with many other new SQL Server features, they'll release it disabled/off by default and force the DBAs to turn it on. At any rate, +1 from me for the deprecation warning. –  swasheck Sep 19 '13 at 2:40
    
It's a good point, you have a good link that seems to backup what you say. But I feel link someone else provided (support.microsoft.com/kb/230785) supercedes it. I'm more likely to think the buffer management section got it half wrong than the other link got it wholey wrong. If that makes sense, not entirely sure I'm putting myself across very well! –  Paul Sep 26 '13 at 10:58
    
It's one of those things like licensing, nothing MS puts out about that is very clear either... –  Shawn Melton Sep 26 '13 at 18:30

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

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/23/checksum-and-tempdb.aspx

share|improve this answer
    
Thanks @DaniSQL, however no one has answered the question in full yet. i.e. when was TORN PAGE DETECTION introduced and how did it behave during upgrades/migrations. –  Paul Sep 18 '13 at 14:11
    
I will leave that for historians to find out :-) As for the upgrade/migrations nothing will happen unless you manually change page verify option to CHECKSUM in each database. Even then already existing pages will not have checksum. blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/29/… –  DaniSQL Sep 18 '13 at 14:25
    
thanks @DaniSQL, that is how I understand migrations to SQL2005 and above to work. I just wanted to make sure prior versions also maintained this behavior –  Paul Sep 18 '13 at 15:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.