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:

DBCC encountered a page with an LSN greater than the current end of log LSN (141131:0:4) for its internal database snapshot. Could not read page (9647:-33648758), database 'PaulsDB' (database ID 26), LSN = (-1302554001:2131886119:4432), type = 255, isInSparseFile = 1.   Please re-run  this DBCC command.

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.

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×