Corruption in the database log

SQL Server Administration
1

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

2

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/

3

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

4

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.

5

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

6

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

7

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.

8

I would like to suggest to follow the below steps:

DBCC CHECKDB: Run DBCC CHECKDB on the database to identify and repair corruption. If corruption is detected then you may need to restore from a good backup if repairs can't be made.

Check Log Error: Before making any changes, you should check the SQL Server error log and system event logs to understand the exact cause of the corruption. This might give you a clearer idea of the issue.

Backup and Restore: A safer method is to take a full database backup, then a log backup (if possible), and restore the database. You can try repairing the corruption during the restore using the WITH CONTINUE_AFTER_ERROR option, but proceed with caution, preferably in a non-production environment.