If you have a database who's page verify option is set to none does
performing a DBCC CHECKDB actually do any page verification?  My intuition
says that it cannot because the pages that were stored would have been stored
without any torn page bits or checksum.  Can anyone verify this for me?

-Mike

It depends on what you mean by "verification". For such cases, checkdb
can't verify torn pages or checksum since they don't exist. But there
are bunch of other types of verifications that checkdb does. Does the
page below to the correct table/index as indicated by the IAM page,
does it point back to the preceding page (index linked list), etc.
Those kind of things.

Show quoteHide quote

Thanks Tibor!  I was speaking of torn page verification.  I also emailed
Paul Randal and he agreed and wrote the following.  (I'll append it in case
if anyone else has similiar questions).  With this information it makes it
apparent that turning off checksum or torn_page verification is a bad idea.
I don't think that BOL does a good job of describing what happen when you
turn off page_verification on a database.  It should better explain the fact
that turning it on after the fact does not offer any protection until the
pages are written back to disk, and that running a DBCC CHECKDB will not
find corruptions in the middle of the data portion of a torn page if
verification is turned off.

-------------------------------------

You're right - if torn-page or checksum isn't turned on then there's nothing
that can be detected as far as page protection options are concerned.
CHECKDB may still pick up on corruptions that it finds from doing all the
consistency checks that it does - but it won't see corruptions in the middle
of data values, for instance.

Ha - that's the bummer about turning on page checksums - nothing happens
until a page is read in, changed, and written back out. The only way to
force pages to get checksums is to make them change - e.g. through
rebuilding all your indexes, whcih may be unpalatable - there's no 'touch'
tool out there at all.

Cheers

Paul S. Randal
Managing Director, www.SQLskills.com
http://www.sqlskills.com/blogs/paul

--------------------------

With a database that has never had any page verification turned on, do our
weekly DBCC CHECKDB’s actually have any means of checking for torn pages?
From what I gather the tornbits / checksum is calculated on the write and if
these db’s have never had it turned on then they have never been written to
disk.  Therefore when the CHECKDB run’s, it would have no means of verifying
if the page is torn or not if the checksum/tornbits data is not available.

If I want to turn on checksum or torn_page verification is there any way
that I can “touch” those pages to force a checksum or torn_page calc?


Show quoteHide quote

Error logs enormous. Can I delete or save elsewhere?
MS Access, ODBC, SQL 2005, delays before data appears
Script to delete records from a table older than N number of days.
in full recovery mode but log keeps self-truncating
Server Disk Space
SQL SErver 64 bit use of memory
Security Question
how to store decimals in tables
The time stamp counter of CPU on scheduler id X is not synchronized with other CPUs
Cumulative package 2 for SQL 2008