none
Page Verify Options and enabling RRS feed

  • Question

  • Good afternoon,

    We have recently upgraded some databases and are now looking to incorporate some of the new functionality (such as using CHECKSUM rather than Torn Page Detection).

    I've tried, in test, to change this using the command :

    ALTER DATABASE <DATABASE> SET PAGE_VERIFY CHECKSUM;

    And this works perfectly.  However I'm not sure when this will actually take effect?  Do I need to restart my database server or will the change be instant and then, the next time a page is loaded, it'll have the CHECKSUM applied and checked?

    Thanks

    Andy

    Thursday, September 6, 2012 3:02 PM

Answers

  • As Rama's link shows, the setting does not affect any pages at all when first turned on. You must first re-write the page before it gets the checksum added. 

    From a design standpoint, Microsoft couldn't just let SQL Server immediately create a checksum for all existing pages - this could result in a lot of activity for databases over 50 GB (remembering this is a feature added in 2005 when 50 GB was a decent sized database), and so it needs to be added in a controlled fashion - hence, whenever we're already taking the time to write the page.

    The best brute-force way to force every page to be re-written (and thus have a checksum added) is to rebuild all your indexes (you'll need to jump through some additional hoops for heaps, however) during an acceptable maintenance window. Until then, you'll still have the torn-page detection protection, but the level of corruption detection is night-and-day between the two.

    • Marked as answer by AndyB1978 Friday, September 7, 2012 7:13 AM
    Friday, September 7, 2012 4:48 AM
    Answerer

All replies

  • Andy,

    see John explaination-

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/dbbaafb8-a9ad-477b-9163-831409c266e9


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Thursday, September 6, 2012 3:08 PM
  • you will certainly not have to restart SQL server for this. I presume this db is not newly created because then the setting would be on by default. In that case only pages will be checksummed when modified.

    Geert Vanhove

    Thursday, September 6, 2012 3:34 PM
  • As Rama's link shows, the setting does not affect any pages at all when first turned on. You must first re-write the page before it gets the checksum added. 

    From a design standpoint, Microsoft couldn't just let SQL Server immediately create a checksum for all existing pages - this could result in a lot of activity for databases over 50 GB (remembering this is a feature added in 2005 when 50 GB was a decent sized database), and so it needs to be added in a controlled fashion - hence, whenever we're already taking the time to write the page.

    The best brute-force way to force every page to be re-written (and thus have a checksum added) is to rebuild all your indexes (you'll need to jump through some additional hoops for heaps, however) during an acceptable maintenance window. Until then, you'll still have the torn-page detection protection, but the level of corruption detection is night-and-day between the two.

    • Marked as answer by AndyB1978 Friday, September 7, 2012 7:13 AM
    Friday, September 7, 2012 4:48 AM
    Answerer
  • Thanks Jim,

    We routinely rebuild the indexes in any case, so this is ideal.

    Regards

    Friday, September 7, 2012 7:12 AM