The following is a compilation of results that I read up on. You will find vastly more information in the linked blogs and documents.
First, it can happen that DBCC CHECKDB
won't detect inconsistencies if you turn off checksum or torn_page verification. A quote from Paul Randal:
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.
The above situation can hit you, if you upgraded a database from SQL Server 2000 or before to 2005 or later. You then need to manually enable page checksums with ALTER DATABASE to get them active. But then the 2nd paragraph of the above quote kicks in and might trouble you.
BACKUP WITH CHECKSUM
will detect checksum inconsistencies, but only if the page already had a checksum written to it, when it is being backed up. Normally DBCC CHECKDB
also detects these errors, so it's not a good idea to "use BACKUP WITH CHECKSUM to replace DBCC CHECKDB".
Now there is a second possibility for DBCC CHECKDB
not to show any inconsistencies, even if there are some. For this I'm just quoting again Paul Randal in "Misconceptions around corruptions: can they disappear?":
So what about the disappearing corruptions? This gets into how consistency checks work. Consistency checks only run on the pages in the database that are allocated. If a page isn't allocated to anything, then the 8192 bytes of it are meaningless and can't be interpreted. Don't get confused between reserved and allocated - I explain that in the first misconceptions post here. As long as a page is allocated, it will be consistency checked by DBCC CHECKDB, including testing the page checksum, if it exists. A corruption can seem to 'disappear' if a corrupt page is allocated at the time a DBCC CHECKDB runs, but is then deallocated by the time the next DBCC CHECKDB runs. The first time it will be reported as corrupt, but the second time it's not allocated, so it isn't consistency checked and won't be reported as corrupt. The corruption looks like it's mysteriously vanished. But it hasn't - it's just that the corrupt page is no longer allocated. There's nothing stopping SQL Server deallocating a corrupt page - in fact, that's what many of the DBCC CHECKDB repairs do - deallocate what's broken, and fix up all the links.
I don't have a final answer to your question, but as DBCC CHECKDB
only checks allocated pages it won't show inconsistencies in deallocated pages. The only situation I can imagine now is that BACKUP also backups those deallocated pages showing potential checksum errors that were skipped by DBCC CHECKDB
.
Best Answer
Yes, there is a suspect_pages table.
Here is more good info on DBCC CHECKDB http://blogs.msdn.com/b/cindygross/archive/2010/06/13/dbcc-checkdb-database-integrity.aspx