0

I recently ran across an issue trying to restore a db and log files. DB restored without issues. About 10 log files in, I received a message stating the LSN was out of sync and could not finish. I used a tool that puts them in order, so that's not my issue. I have my log backups set to run in 15 minute intervals. This large database takes a couple of hours to perform a full backup. Could the log backups happening simultaneously during the full backup cause this situation?

The end result was not bad this time, but this is alarming. I am trying to maintain a RPO of 15 minutes. This little test resulted in an 8 hr RPO.... unacceptable.

Regards,

Tim

1 Answer 1

0

Could the log backups happening simultaneously during the full backup cause this situation?

No, simultaneous full and log backups have been possible since SQL Server 2005, as Paul Randal states here in a post on concurrent log / full backups.

Here’s a question that came in – what changed in SQL Server 2005 that allows concurrent log and full backups?

In SQL Server 2005, the restriction was lifted, but there’s a subtle twist. You can do concurrent log backups with fulls BUT the log is not cleared when the log backup ends. The clearing of the inactive portion of the log is delayed until the full backup completes. This could cause you to have disk space problems if your log generation rate is huge and you’re relying on very frequent backups to manage the log size.

Also stated here regarding the many myths regardings backups.

I used a tool that puts them in order, so that's not my issue

If it is not the tool's wrong doing as you mention then I would try to look if there are any other log backups being taken in your backup history dmv's in the MSDB database.

Double check the restore procedure as well so that you are certain that you have all the log files (for example different paths after failing over the Always On Availability Group if the database is participating in an Availability Group, however Ola's procedure should take care of that).

2
  • Thanks Randi. I ran the query you suggested and see that after the full backup, several log backups are created that look normal. Then I see one with a NUL physical device name (no file location, but has a backup size) followed by other normal log backups. Oddly, I was able to restore the one right after the null entry. But the 2nd one after the NUL entry failed with an out of sequence message. Dec 14, 2020 at 23:43
  • @TimShelton this should be the issue then, I am not sure why one of these did not give the error however. Best to investigate if there is some process backing up the LOG to 'NUL' and breaking the log chain. Dec 16, 2020 at 9:27

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.