SQL Server: Enable CHECKSUM Page Verification to detect a prime spot of Data Corruption

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

From the MSDN,

“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 read from disk, the checksum is recomputed and compared to the checksum value that stored in the page header. This helps provide a high level of data file integrity.“

Guys, You have two options, wait for huge data corruption and detect it a very first level of data corruption.

Once you enable CHECKSUM, every read off and write off a check for CHECKSUM and validates it. If it finds any mismatch or broken chain of CHECKSUM value, it indicates data corruption immediately.

CHECKSUM option doesn’t prevent the data corruption, it gives you more confidence against data corruption, and you can detect data corruption error faster and solve it in the better way.

It occupies little CPU I/O for maintaining a CHECKSUM values, but it is acceptable.

Find all databases which are not configured for CHECKSUM verification:

Enable CHECKSUM for your Database:

Important Note:
Once you enable and CHECKSUM for your database, It does not instantly add CHECKSUMs to your existing data pages in the database. You have to read each page using INSERT/UPDATE/DELETE or simply you can execute REBUILD option for your indexes.

Other related articles:

SQL Server: Monitor and Manage Database Corruption