Recovery Page Verify Checksum and BACKUP With CHECKSUM

Question

Recovery Page Verify Checksum and BACKUP With CHECKSUM

ZeeAtl

SSCommitted

Points: 1569
More actions
December 9, 2009 at 9:15 am

#135812

What are the advantages of using Checksum both in the Database Options Recovery->Page Verify
and when doing BACKUP of database WITH CHECKSUM of database set with Checksum (if they
are related)?
Zee (Atl)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Page verify checksum.

Pro: If the IO subsystem messes up and corrupts a SQL page, any read of that page will pick it up immediately. Since the checksum is the last thing calculated before the page is written and the first thing checked when it's read back, an incorrect checksum means that something outside of SQL Server modified the page.

Con: A very small CPU overhead. I think 2% was mentioned

Backups checksum

Pro: A checksum is calculated over the entire backup. Any change to the backup file will be detected by a restore or restore verify only and so it's easy to tell if the backup file has been damaged in any way. Without checksum, that requires restoring the backup and then (for 100% safety) running CheckDB

Con: Not sure. Probably a small CPU overhead on the backup process.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

I'd recommend reading the following blog posts from Paul Randal about this:

How to tell if the IO subsystem is causing corruptions?

Example 2000/2005 corrupt databases and some more info on backup, restore, page checksums and IO errors

BACKUP WITH CHECKSUM only validates pages that have a checksum already calculated on them and will fail if there is a CHECKSUM mismatch. This doesn't help much if your database was using TORN PAGE DETECTION under 2000 and you didn't move to CHECKSUM after upgrading to 2005, or if you moved to CHECKSUM, but the pages haven't be rewritten back to disk, which is when the CHECKSUM is calculated.

Deb Anderson SSCrazy Points: 2116 More actions · Answer 3

I have i/o subsystem traumas on a fairly regular basis. I have migrated all databases from 2000 to 2005 and then to 2008. Some of the databases born on 2000 unfortunately made it all the way to 2008 without enabling PAGE VERIFY = CHECKSUM. I am trying to enable the CHECKSUM going forward, and I know I have to rebuild the clustered indexes to do this.

I am having a bit of a disconnect though, and need a second set of eyes, so to speak ... surely it won't work to do an ALTER INDEX ... REBUILD ... I have to actually drop the clustered index, which forces a rebuild of the secondary indexes. Otherwise, I don't see how every page would have a checksum added to it.

So essentially my process would be, 1) drop all the secondary indexes 2) drop and recreate the clustered index and 3) recreate the secondary indexes.

thanks for any thoughts ...

😎

Deb Anderson SSCrazy Points: 2116 More actions · Answer 4

Thanks ... but I think I have my answer ... BOL ... http://technet.microsoft.com/en-us/library/ms189858.aspx

ALTER INDEX ... REORGANIZE will only organize the leaf level pages of the clustered index which improves index scanning

ALTER INDEX ... REBUILD actually does drop the clustered index and create a new one. ERGO, every page in the table is touched and thusly gets a checksum applied.

😎