none
Is it best Practise to enable Torn page detection for SQL Server 2000? RRS feed

  • Question

  • Is it best Practise to enable Torn page detection for SQL Server 2000?

    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.

    Is it best Practise to enable Torn page detection for SQL Server 2000?

    I understand the CPU performance of enabling would be between 1 - 2 %.


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Monday, May 31, 2010 7:31 AM

Answers

  • Yes, you want as much protection as possible. Torn page detection wasn't default in 7.0, but was in 2000. In 2005, the default is the higher checksum setting default. Upgraded databases will have their old setting, so it is a good thing to set protection for these to highest level.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Monday, May 31, 2010 10:43 AM
    Moderator

All replies

  • Yes, you want as much protection as possible. Torn page detection wasn't default in 7.0, but was in 2000. In 2005, the default is the higher checksum setting default. Upgraded databases will have their old setting, so it is a good thing to set protection for these to highest level.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Monday, May 31, 2010 10:43 AM
    Moderator
  • Yes, sqlserver 2005 onwards default it is enable, sqlserver 2000 you need to enable manually. check the below article for the concept of torn page detection.
    One of the most common problems is when a power failure occurs and a disk drive is in the middle of writing out a database page. If the drive cannot complete the write before it runs out of power (or write operations are cached and there isn't enough battery backup to flush the drive's cache) the result could be an incomplete page image on the disk. This can happen because an 8KB database page is actually comprised of 16 contiguous 512-byte disk sectors. An incomplete write could have written some of the sectors from the new page but leave some of the sectors from the previous page image. This situation is called a torn page. How can you detect when this happens?
    SQL Server has a mechanism to detect this situation. It involves storing a couple of bits from every sector of the page and writing a specific pattern in their place (this happens just before the page is written to disk). If the pattern is not the same when the page is read back in, SQL Server knows the page was "torn" and raises an error.
    In SQL Server 2005 and later, a more comprehensive mechanism called page checksums is available that can detect any corruption on a page. This involves writing a whole-page checksum on the page just before it is written out and then testing it when the page is read back in, just as for torn-page detection. After enabling page checksums, a page needs to be read into the buffer pool, changed in some way, and then written out to disk again before it is protected by a page checksum.
    So, it is a best practice to have page checksums enabled for SQL Server 2005 onwards, with torn-page detection enabled for SQL Server 2000.
    To enable page checksums
    ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;
    To enable torn-page detection for SQL Server 2000
    ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;
    Monday, May 31, 2010 12:12 PM
  •  

    Thanks Tibor for clarifying my queries.

    Also, I have found the article Brad McGehee on SQL Server Database Settings Performance Checklist


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Tuesday, June 1, 2010 12:13 AM