locked
What precaution do I need to take to change the value of Page Verify Option? RRS feed

  • Question

  • Hi,

    We would like to update the Page Verify option to “CHECKSUM”.

    May I know what precaution do I need to perform and is that require any downtime? And do we need to restart sql services?

    ThThanks

    • Moved by Tom Phillips Friday, February 8, 2013 1:27 PM Possibly better answer from Database Mirroring forum
    Friday, February 8, 2013 5:01 AM

All replies

  • You can set it without restart where it will take immediately-

    ALTER DATABASE <yourdb name here>
    SET PAGE_VERIFY CHECKSUM
    GO




    But it gets affects on the pages when that writes to disk after enabling checksum, also if you need you can monitor the server.

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, February 8, 2013 5:15 AM
  • Thanks Rama,

    But my database is part of mirroring and it’s in mirror, synchronized/restoring mode. What steps do I need to perform to change the value of Page Verify option to “CHECKSUM”?


    • Edited by TwoR Friday, February 8, 2013 5:40 AM
    Friday, February 8, 2013 5:36 AM
  • usually Page checksums are on by default for all databases created on SQL Server 2005 and SQL Server 2008 or set for Model,also which version of SQL that you are using or Did recently you have  migrated the databases,if so you have been done after the migration for changing to checksum &  configure the mirroring?

    In mirror side it is always in synchronized/restoring mode and you cannot do, you need to do  on the Principle server side but Iam not sure that(Might be someone can help on this) you can do on the principle but before configuring can do then configuring the database mirroring, I guess.

    Also in  2008 there is an automatic page repair in DBM(Enterprise) but RCA must be do after resolve by incase.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    • Edited by Rama Udaya Friday, February 8, 2013 6:57 AM
    Friday, February 8, 2013 6:11 AM
  • My mirrored server running on 2008 R2 (SP1) - 10.50.2500, my principle database running with Page Verify option 2 whereas my mirrored database is on Page Verify option 1. Can anyone help me how should I change the Page Verify option to 2 on my mirrored database?



    • Edited by TwoR Friday, February 8, 2013 10:15 AM
    Friday, February 8, 2013 10:14 AM
  • Hi Randive09-

    Anything you execute on the pricipal will also be applied to the mirror side - no two ways about it.  I don't know how you could even determine the properties of the mirror DB since it is in restoring state, but I think you can be sure that if your principal DB is CHECKSUM, you mirror DB is as well.

    If you really really really need proof, fail-over the session and check the page_verification on the new principal, or break the mirror, recover the mirror db and take a look before rebuilding the mirror.

    Good luck --Andy

    Saturday, February 9, 2013 5:50 AM
  • Thanks Andy.

    To answer your question, I have run below query and found page_verify_option for particular database is 1.

    select page_verify_option, * from sys.databases

    Tuesday, February 12, 2013 3:13 AM
  • Hey Randive09 - yep i see your point ;)  db properties can be selected from sys.databases on a mirror db.

    I have recreated your same situation and see the same thing - altering the page_verify setting is not reflected in the mirror db. 

    My take on it now (just speculating) is that the sys.databases view is updated upon the initial restore (i.e. mirror init) with the properties of the DB at that time. The view sys.database lives in master so no problem selecting from it but it will only be current to the mirror-init-restore. Any db-specific properties query cannot work b\c the db is restoring.

    I'd still say a change on the principal is reflected on the mirror side - even if sys.databases does not show it (at least not untill untill the db comes out of recovery).

    Good luck -Andy

    Wednesday, February 13, 2013 5:36 AM