Torn page detection

  • thinknight

    SSCrazy

    Points: 2068

    Torn page detection is a select box in database properties tab.

    What does it mean ? Why is it used for?

    Does it effect performance? Do Every tempdb databases carry this check box on?

    Does it directly effect tempdb database?

    Thanks

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Every database should have this on in SQL 2000. In 2005 it's been replaced by page checksums which should be used instead.

    It's a way for SQL to detect that a page has been damaged, either due to an error writing the page to disk, or a misbehaving IO system.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Schadenfreude-Mei

    SSChampion

    Points: 14083

    GilaMonster (11/13/2008)


    Every database should have this on in SQL 2000. In 2005 it's been replaced by page checksums which should be used instead.

    It's a way for SQL to detect that a page has been damaged, either due to an error writing the page to disk, or a misbehaving IO system.

    🙂 I really am such a massive fan of your Gail 🙂 You always have the answers.

    Please dont get upset with me agian, I mean this with all respect. Your my Hero, err um Hero-ess :blush:

    Have a g reat weekend.

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • sivaprasad

    SSCertifiable

    Points: 6183

    Torn I/O is often referred to as a torn page in SQL Server documentation. A torn I/O occurs when a partial write takes place, leaving the data in an invalid state. SQL Server 2000/7.0 data pages are 8 KB in size. A torn data page for SQL Server occurs when only a portion of the 8 KB is correctly written to or retrieved from stable media.

    SQL Server always checks I/O completion status for any operating system error conditions and proper data transfer size and then handles errors appropriately. Torn pages generally arise after system outages where the subsystem does not complete the entire 8-KB I/O request.

    Disk drive manufacturers generally limit data transfer guarantees to sector boundaries of 512 bytes, so if the stable media is a physical disk and the controller does not contain a cache with battery backup capabilities, the I/O request is limited to the final spin/transfer rate of the physical drive. Thus, if an I/O is issued to write 8 KB (for a total of sixteen 512-byte sectors), but only the first three sectors make it to stable media, then the page becomes torn resulting in data corruption. A subsequent read of the 8-KB page would bring in 3 sectors of the new version of the page and 13 sectors of an older version.

    SQL Server can enable torn page detection on a per-database basis. A portion of the first 512-byte sector of the page contains the page header. When torn page detection is enabled, the header member contains information about each of the other 512-byte sectors or segments of the 8-KB page. When the page is read in, the torn page information is used to detect a torn page situation.

    Torn page detection incurs minimal overhead and is a recommended practice for SQL Server installations

    Starting with SQL Server 2005 database level page verify "CHECKSUM" is the default and in SQL Server 2008 it is enabled for "tempdb" too.

    However "Torn page detection " is not default for SQL Server 2000. Also databases upgraded from previous version on SQL Server 2005 and 2008 will not have "Page Verify" enabled.

    References: SQL Server 2000 I/O Basics

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • TheSQLGuru

    SSC Guru

    Points: 134017

    I highly recommend you reference Books Online. A WONDERFUL resource.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • EL_oh_EL

    Old Hand

    Points: 347

    I know this is a old thread but i have a question 🙂

    So if I have databases running in SQL 2000 Compatibility mode on a SQL 2005 standard server, do i turn checksum for every database including the ones running in SQL 2000 compatibility mode?

    Thanks!

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Yes.

    Please in future post new questions in a new thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • EL_oh_EL

    Old Hand

    Points: 347

    Thanks!

    will do! 🙂

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply