Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following:

BACKUP 'BrokenDatabase' detected an error on page (1:123456) in file ’BrokenDatabase.mdf'.
Error: 3043, Severity: 16, State: 1.

In the linked question and the backup I have ready for DBCC PAGE investigations, DBCC CHECKDB passed without error but corruption is evidently present.

What types of corruption can occur whereby CHECKDB will pass but a BACKUP WITH CHECKSUM will fail?

share|improve this question
1  
Maybe, DBCC IND: command provides the list of pages used by the table or index? You can look at table, index where the problem is. –  garik Aug 31 '11 at 23:40
1  
I did a quick analysis of the pages that threw errors when the issue occured. The 30 minute study concluded that I'd need more than 30 minutes to work out what was wrong :) When I get back to looking at it in more detail I'll post a seperate question with specifics from that case. –  Mark Storey-Smith Aug 31 '11 at 23:50
add comment

1 Answer

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 in this post:

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.

share|improve this answer
    
Have most of Paul's articles already bookmarked but +1 for the summary. None of these apply to the database I have set aside so hoping others may add further thoughts. –  Mark Storey-Smith Sep 7 '11 at 16:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.