Search This Blog

Loading...

What's the best PAGE_VERIFY setting

I always set this value to CHECKSUM. When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.

The following query, generate a script to  change the page verify option to CHECKSUM in case its different.

-- Generate ALTER DATABASE statements to change Page Verify option to CHECKSUM
SELECT N'ALTER DATABASE [' + db.name + N'] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;'
FROM sys.databases AS db
WHERE db.page_verify_option_desc <> N'CHECKSUM';




 



Note: Remember, for existing database, the changes wont take effect immediately after you make the change. The database engine will calculate it only when the page is read thru any of  DML statement.



 



 



For more info check this post  http://www.littlekendra.com/2011/01/25/pageverify/