2

I am investigating an issue where on a somewhat predictable schedule where our third party backup software begins a full backup of some of our databases during the workday. It starts a log backup, detects a broken chain in the LSN's and then converts it to a full backup.

I checked and there are no other log backups being run against the SQL servers we are experiencing this with.

What happens is that a log backup begins on schedule, but on some days the connection with the backup server is lost during the backup. The job sits in limbo and when it restarts with the same job id there is a broken chain detected and it begins a full backup of affected databases.

Scouring the logs it I can see the backup failures and the backup restarting with the same LSN which is no longer there.

So my question is, when there is a log backup with the truncate option does SQL Server truncate the log as it is backed up, or at the successful completion of the job? In my case it seems like a range of LSN's is being backed up, marked as backed up and when the job fails it looks for those same LSN's again when the job restarts.

  • I've checked the entries in the SQL log and in the backupset table and it's only our backup app that is running the backups. But on a certain day of the week, at a certain time of day half of the jobs hang because the backup server goes down for a few minutes and when they restart again they begin a full backup on some databases. And from what I can tell it looks for the LSN, runs a backup, job fails, restarts looking for the same LSN. – Alen Apr 24 '18 at 16:10
  • We've changed the log backup schedule to work around this, but trying to find the root cause of what happened in the past 3/4 weeks at the exact same time. – Alen Apr 24 '18 at 16:14
5

Technically, the virtual log files (VLFs) are only attempted to be marked as inactive (can be reused) when they have been successfully backed up and are no longer needed by internal (and sometimes external) processes - such as replication or availability groups.

What happens is that a log backup begins on schedule, but on some days the connection with the backup server is lost during the backup.

This shouldn't cause any issues with SQL Server. SQL Server will see that the connection was broken and kill the session plus whatever was executing. This means that backup wasn't successful so the next log backup should start at the same place because it hasn't yet finished successfully.

The job sits in limbo and when it restarts with the same job id there is a broken chain detected and it begins a full backup of affected databases.

Sounds like the application thinks it ran a backup successfully even though it didn't and "detects" there is a problem... but there isn't. It really sounds like the application logic in the backup program is either flawed or running into a config issue... or it might be their standard logic (by design).

Now if it takes a full backup and walks away... that's not going to affect the log reuse.

But on a certain day of the week, at a certain time of day half of the jobs hang because the backup server goes down for a few minutes and when they restart again they begin a full backup on some databases. And from what I can tell it looks for the LSN, runs a backup, job fails, restarts looking for the same LSN.

Sounds like you have other infrastructure issues that may be contributing. Assuming they are not, though, it sounds like the backup application is confused. If the job fails to backup the log successfully it should start at the same place because it hasn't yet been successfully backed up. If the backup application chooses to see that as an issue and take a full backup to reset itself (the backup application) internally, that's on the application vendor to fix/decide/working as intended/whatever after you tell them about it.

However, since this only happens when the backup application server goes down... you may want to also have a stern talking to your infrastructure team and get that issue situated - or at the very least, if it's downtime because of patching or something, that all jobs be held until the patching (or whatever) is completed.

|improve this answer|||||
  • Had a chat with support from the vendor today, and they said virtually the exact same thing. – Alen Apr 26 '18 at 18:37
  • @Alen Did they say it was by design on their end or if they were going to work with you to get it changed? – Sean Gallardy Apr 26 '18 at 18:45
  • 1
    By design and due to feedback from large customers. There is a workaround to not run log backups during specific times. – Alen Apr 26 '18 at 19:27
  • @Alen Good to know it's by design, even though it may not be what you wanted. I'd now investigate with your team why the server is having issues during these times. That seems like the logical next step. Thanks for keeping us updated :) – Sean Gallardy Apr 26 '18 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.