Resolving the “Backup detected log corruption in database”

I saw this in the SQL logs today. It looks like some corruption happened in the transaction log of a specific database. I saw a few different ways to approach this but saw the most common way (as a first attempt) was to simply set the database recovery model to SIMPLE, run a CHECKPOINT; manually, and then set the recovery model back to whatever it was. If it was on FULL previously and you had log-shipping enabled, you will need to rebuild it.

Here is a blog post by Paul Randal explaining how DBCC CHECKDB will work in this scenario and what it will not do. He also recommends using the approach I mentioned about with flipping the recovery model: Transaction log corruption and DBCC CHECKDB

Here is the beginning of the SQL log. As you can see, it looks like everything checks out ok.

Date 4/13/2016 8:17:51 PM
CHECKDB for database ‘MyDatabase’ finished without errors on 2016-04-02 20:00:15.827
(local time). This is an informational message only; no user action is required.

then I saw this later in the logs:

Date 4/13/2016 8:30:21 PM
Backup detected log corruption in database MyDatabase. Context is FirstSector.
LogFile: 2 ‘D:\SqlData\MyDatabase_log.ldf’ VLF SeqNo: x28985 VLFBase: x82000
LogBlockOffset: x91a00 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x700070
LogBlock.StartLsn.Blk: x770034 Size: x50 PrevSize: x33

I simply set the recovery model to SIMPLE, ran a CHECKPOINT;, and reset it back to FULL and that solved the issue. I was able to do those steps in less than 2 minutes. The database was fairly small. I then rebuild log-shipping and everything was back up.

Views – 870