DB Options: Page Verify

https://youtu.be/eXLELsEVAP0

It’s become significantly rarer for database corruption to occur nowadays because of hardware and software improvements. In older version of SQL Server it was fairly common for database pages to be corrupted when written to disk especially during power failures etc. Page Verify is one of the options that SQL Server uses to identify if pages that have been modified outside of transactions in SQL Server. It is important for the database to identify if pages have been modified outside SQL Server to maintain ACID properties. To this end Page Verify inserts a checksum to the page header. On the next fetch SQL Server verifies the checksum against the page to identify if it has been corrupted when it was written to disk.

Torn page detection was a method used prior to SQL 2005 to perform a similar consistency check on data being read from Disk. However it was not as comprehensive as CHECKSUM. Page verify Checksum is the default option for the database and should be left alone. Do not set Page Verify to none as it prevent SQL Server from performing any kind of check on the data and even DBCC CHECKDB will not catch the corruption after that point.