How to Resolve database log file corruption

Hi Folks – Before Some time we faced an issue when log backup and CDC.<db_name>_capture jobs for a database were continuously failing.  After checking the SQL error log we found below error message :

2013-12-17 12:15:12.450 spid344 Backup detected log corruption in database Gaurav. Context is FirstSector. LogFile: 34 ‘K:\Mount12\Gaurav_Log\Gaurav_Log.ldf’ VLF SeqNo: x18e44eVLFBase: x374000000LogBlockOffset: x3777c4200SectorStatus: 2 LogBlock.StartLsn.SeqNo: x3020001LogBlock.StartLsn 

2013-12-17 12:15:12.450 Backup       Error: 3041, Severity: 16, State: 1.

As in above message its clearly stating that log file for a db has been corrupted. Since it was a 4 TB and prod db so we couldn’t afford a downtime. We implemented below steps to remove the corruption from db log file.

  1. Change the recovery model for culprit db to simple by using command Alter database <db_name> set Recovery Simple.
  2. Shrink the corrupted log file by using Wizard to its max. extent. This way SQL returned the corrupted and free space part to OS. Now OS will not write any data to bad sector if its have.
  3. Take the full backup for database and it should complete this time w\o any log file corruption error. 
  4. Change the db to full recovery model and again initiate a Full backup, it should also get success.

Hope these steps will help you.

Cheers : Gaurav

Advertisements

2 Responses

  1. Hi Gaurav, I have a similar issue and attempted to use your guidelines. I am not clear about the 2nd step. Can you please explain this in detail. Thanks.

    • Hi Rajesh – You need to shrink the corrupt log file as per step 2, how much you can i.e. if your log file occupied 10 GB size and available free space showing by wizard (Shrink file wizard) is 6 GB then it means that you can shrink the file to 4 GB and can return 6 GB to OS.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: