I have a SQL Server database with the PAGE_VERIFY option set to NONE. When I set this to CHECKSUM, only pages changed from that point onwards get their checksum calculated and stored on the page.

I'm looking for the best way to put checksums on at least all table and index data.

My initial idea would be to drop and recreate all clustered indexes. For tables that have no clustered index I would create one and then drop it again.

That should result in at least one write operation on each data and index page, but it's a bit heavy-handed. Anyone has a better idea?

1 Answer 1


The most comprehensive way in my view would be to encrypt/decrypt the database with TDE. This will ensure that each and every page will change in memory and will be flushed to disk.

I've tried this with success on 'legacy' dbs that were originally created in SQL2000, after I discovered that several pages didn't have actual checksums on them (0x...200) if you look at the header with dbcc page.

If you were to try this, I would recommend testing it on a restored version of the live db, just in case you have undetected corruption that could be caught and stall the encryption process. There are flags to deal with it, but better play it safe.

Obviously you'll want to backup the certificate used by the encryption, so you are covered for any eventuality during the time the db is encrypted.

If anyone has a better idea for writing checksums on all pages, I'd love to hear it :-)

  • That's a good one, especially since it catches all pages in the DB and runs in the background. Only issue is if you have to deal with a restore from that timeframe but that's just the usual TDE stuff. Hottest candidate for accepted answer so far :-)
    – TToni
    Jul 18, 2013 at 9:25
  • 1
    I like it because it would also ensure checksums are on pages that don't belong to tables/idxs.
    – John Alan
    Jul 18, 2013 at 12:40
  • For future readers, I deleted my answer because TDE would touch the entire DB while rebuilding indexes won't.
    – Jon Seigel
    Jul 18, 2013 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.