Transaction log corruption and DBCC CHECKDB

In a departure from our usual Q&A style I want to post a follow-on to my previous discussion about how transaction log corruption can lead to transaction log backup failures, and how to recover from them (see here).

Any operation that tries to use a corrupt log record will encounter failure, and DBCC CHECKDB is one of those operations.

By default, DBCC CHECKDB will create a hidden database snapshot under the covers to provide a transactionally-consistent view of the database on which to run the consistency checks. The process for creating a database snapshot is to checkpoint the real database, and then essentially run crash-recovery on the real database, but into the context of the database snapshot – not affecting the real database. This pseudo-crash recovery rolls back the effect of any uncommitted transactions that are occurring in the real database, making the database snapshot consistent.

If this process encounters a corrupt transaction log record, then the database snapshot creation will fail – leading to the DBCC CHECKDB failing too. A bunch of errors will be generated, including one that identifies the corrupt transaction log record, like below:

The page ID and its LSN are obviously completely wrong.

All is not lost, however, as there are two ways around this issue.

Firstly, you could employ the technique I described in the blog post I referenced above (switch to the Simple recovery model, checkpoint to truncate the log, and switch back to the Full recovery model), but you’d have to make sure there are no uncommitted transactions otherwise the transaction log may not truncate past the corruption.

Secondly, you could use the WITH TABLOCK option for DBCC CHECKDB. This will skip creating a database snapshot and will instead require a short-term database exclusive lock while allocation consistency checks are completed, and then table share locks as each table is consistency checked. This is the offline version of DBCC CHECKDB, but it won’t fall foul of the transaction log corruption.

Whichever method you use, don’t forget to perform root-cause analysis to determine what caused the transaction log corruption in the first place.

Note: Although I’ve talked about DBCC CHECKDB in this post, everything here also applies to DBCC CHECKALLOC, DBCC CHECKTABLE, DBCC CHECKFILEGROUP, and DBCC CHECKCATALOG.