SQLTeam.com | Weblogs | Forums

Corruption in the database log

Evening all,

The last couple of evenings I've been recieving an error on one of the backups:

BACKUP detected corruption in the database log. Check the errorlog for more information. BACKUP LOG is terminating abnormally. Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've done some googeling and a few people are saying that i can change the database in question to 'Simple Recovery Model' then back to 'Full Recovery Model'

Is this good advice, is that all there is to it or is there a better solution to my error?

Many thanks

Dave

Before you do anything - you need to determine what caused the corruption in the first place and address that issue. If not - it will just happen again.

The next thing you need to do is look in the errorlog to determine the actual error. Once you have identified the error - then you can follow up on how to fix the problem.

And finally, switching recovery model does not - in any way - alter or change the transaction log. All it does is changes when the log is truncated (cleared).

There are several ways to fix the transaction log - but it will depend on the issue. You can review this article for further information: https://www.sqlshack.com/how-to-rebuild-a-database-with-a-corrupted-or-deleted-sql-server-transaction-log-file/

Thanks Jeff,

The corruption happened immediatly after a UPS failed and powered the server off :frowning:
After a few checks everything else looks to be fine, just the one transaction log...

Sorry, I thought I had looked in the correct errorlog, which one should I be looking in? The log i looked in was the backup jub history?

Thats a nice link you sent, very informative. After reading it I think I have a slightly different problem as the database comes on line with out any errors and is usable, its only when the backup job runs it has an issue....

Thanks

Dave

You need to look in both the SQL Server logs - and the system event logs on the server. Corruption happens because of hardware issue and if those issues have not been addressed they will occur again.

Your problem is a corrupted transaction log - to fix that you have to rebuild it and that documentation shows several different options you can try.

If you cannot fix this, then you cannot backup your database - if you cannot backup your database and something like this happens again but now it happens to your data file - then you will lose data and the system will not be recoverable.

:+1: Understood, thank you for your straight talking.

Interesting turn of events. After reading that link a good few timeslast night, Iogged on this morning all set to see if I can fix the transaction log... The backup was sucessfull last night. Just to be sure, I manually ran the backup and again it completed again with out issue. Now i'm a little confused how it managed to fix its self...?

Thanks

Dave

I doubt it fixed itself - most likely the transaction log moved past the corruption in the file so it was no longer trying to read data from that portion of the file. I would run an integrity check on that database and verify no integrity issues show up.