Wednesday, May 7, 2014

SQL Server Transaction Log Corruption & Recovery


In the field of database, a transaction log is defined as a record of actions. Transaction log records all the actions performed in the database. It is an important component of any database because it helps in recovery of database during disaster strike to the database. In SQL server, Every database has a transaction log that list all the changes done in the SQL server database.

For smooth functioning of your SQL server database, you need to monitor and backup transaction log of your database periodically. Frequency of taking log backups depends upon your business requirement. Specially how much time you can afford your business down. It might be 30 minutes to daily. But I would suggest that a frequent transaction log backup minimize the chance of data loss.

Sometime back I faced transaction log backup issue. Backup job was failing continuously and through below error message:

2014-04-28 14:22:15.48 Backup Error: 3041, Severity: 16, State: 1.
2014-04-28 14:22:15.48 Backup BACKUP failed to complete the command BACKUP DATABASE willium. Check error log for detailed messages.

Above error message clearly indicates that backup was failing on the willium database. I checked errorlog for detailed information and got below error message:

2014-04-28 14:22:15.48 Backup detected log corruption in database willium. Context is FirstSector.LogFile: 34 ‘K:\Mount12\willium_Log\willium_Log.ldf’
VLF SeqNo:x18e44eVLFBase:x374000000LogBlockOffset:x3777c4200SectorStatus:2 LogBlock.StartLsn.SeqNo: x3020001LogBlock.StartLsn

As above error messag indicates log file of willium database had been corrupted.

Solution: I had performed below steps to remove corruption from transaction log file of willium database.

  1. First of all, I changed the recovery model of willium database from Full to Simple. You can do this by Alter command. Alter database set Recovery Simple.
  2. Then after shrinked the willium database log file.
  3. Take a full backup of database and it was accomplished without any error message.
  4. And finally changed the recovery model of willium database from Simple to Full.
  5. Again take full backup of database and it was successfully accomplished.   

2 comments:

  1. Awesome information.
    Keep posting the good work.Some really helpful information in there.
    Thanks!
    Disaster recovery

    ReplyDelete