DBCC CHECKDB checks CHECKSUM even if Page Verify = NONE?

Question

Page verify is an option you can define on a database.

SQL 2000: NONE or TORN_PAGE_DETECTION

SQL2005+: NONE, TORN_PAGE_DETECTION or CHECKSUM

I have read a lot about it and understand that CHECKSUM is recommended, and that it doesn't automatically gets turned on when migrating databases from SQL 2000. And even if I turn it on, the pages won't be set with checksum until it is read into the buffer pool, modified, and written back to disk again.

My question:

If I don't have "Page Verify: CHECKSUM" enabled on my database, will it not be checked if I run DBCC CHECKDB? Or will DBCC CHECKDB calculate its own checksum temporarily on the fly somehow??

We have some databases which does not have checksum enabled, and I'm afraid we might have undetected corruptions even though we run DBCC CHECKDB periodically?

Answer 1

Hi,

You can find the answer is below post of Paul Randal

http://www.sqlskills.com/blogs/paul/post/misconceptions-around-corruptions-can-they-disappear.aspx

If you torn page detection enabled, checkdb can also discover torn page errors.

http://technet.microsoft.com/en-us/library/ms176064(v=sql.90).aspx

---

- Chintak (My Blog)

Answer 2

Calcaulating its own checksum is a meaningless process. If you read a page which is bad, and calculate a checksum from that, then the "badness" will be in that checksum. Now calculate the checksum again, and it will also have the "badnedd" in it. The whole point to this is to calculate the checksum after a modification and when you write the data. So what you want to do is to set page verify to CHECKSUM and hope that you don't already have corruptions untected by "NONE" or "TORN PAGE DETECTION".

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 3

Thank you both for your replies. I read the post by Paul Randal and that answered my original question. CHECKDB won't find out if the page if corrupt if CHECKSUM is not enabled.

But that leads me to my next question. If CHECKDB cannot tell if a page is corrupt or not when CHECKSUM is disabled, then what good is CHECKDB WITH PHYSICAL_ONLY for those databases? What can it detect then, if not corrupt pages by checksum?

Answer 4

CHECKDB with PHYSICAL_ONLY does a few thinks more than just check the CHECKSUM. See http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-consistency-checking-options-for-a-vldb.aspx.

---

Tibor Karaszi, SQL Server MVP | web | blog