(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
A few people have suggested some of the myths around page checksums so today is another multi-mythbusting extravaganza! Well, I get excited at least :-)
I described page checksums in depth in the blog post How to tell if the IO subsystem is causing corruptions?
Myth #17: variety of myths around page checksums.
All of them are FALSE
17a) page checksums are enabled automatically when you upgrade from SQL Server 2000 or 7.0
No. You must explicitly enable page checksums on upgraded databases using ALTER DATABASE blah SET PAGE_VERIFY CHECKSUM. Databases that are created on later versions will have page checksums enabled automatically unless you change the setting in the model database – which you shouldn’t.
17b) page checksums are error correcting
No. Page checksums can detect errors in a page but are not like CRC-based checksums in network protocols that can correct single-bit errors.
17c) enabling page checksums kicks off a background task to put a page checksum on each database page
No. There is no process, background or otherwise, that can put a page checksum on each page. This is a major bummer (technical term :-) as it means you must perform index rebuilds or other size-of-data operations to actually put a page checksum on the pages. This myth goes hand-in-hand with 17d below…
17d) simply reading the pages is enough to put a page checksum on them (e.g. with a backup or DBCC CHECKDB)
No. A page checksum is only put on a page when it is read into memory, changed, and then written back out to disk.
17e) when a database is changed from torn-page detection to page checksums, all torn-page detection is lost
No. Pages know whether they are protected through torn-page detection, a page checksum, or nothing at all. As mentioned above, pages aren’t changed to a page checksum until they’re physically altered. I went into this in great detail with an example script in the blog post Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?
17f) page checksums detect corruption immediately
This myth was suggested for debunking by fellow MVP Gail Shaw (twitter|blog) and is of course untrue. A damaged page cannot be detected until it is read into memory and the buffer pool checks the validity of the page checksum.