Q: Every so often our system produces an 824 error and our weekly consistency-checking job fails. However, when I run DBCC CHECKDB during the day there are no errors. What’s going on?
A: This phenomenon is fairly common—I call it “disappearing corruptions,” although that’s a little bit of a misnomer. It can be very frustrating, especially when it happens repeatedly, to have your automated consistency checking job fail or to be alerted to an 823/824 corruption problem, but have nothing show up when you run DBCC CHECKDB manually. How can these corruptions just go away? There are two answers that explain this phenomenon: changes to the database and transient I/O subsystem problems.
When DBCC CHECKDB completes, it’s giving you information about all the data file pages that comprised the database at the time that DBCC CHECKDB started running. This is because it creates a database snapshot when it begins, in order to provide an unchanging, transactionally consistent view of the database, which makes the consistency checks much simpler to engineer. This means that if anything in the database changes between successive executions of DBCC CHECKDB, the set of database pages being checked might be different. If a page was corrupt on one execution of DBCC CHECKDB, it might have been deallocated, and therefore is no longer part of the database on the next execution of DBCC CHECKDB. This means it won’t be processed by DBCC CHECKDB, so the corruption won’t be reported any more—and will seem to have disappeared.