0

now i had one mssql 2019 alwayson cluster.i backup database on master node using monthly fullbackup. weekly differential backup. daily log backup. but unfortunately then backup log chain always broken include log backup and differential bakup .i check the log chain like below:enter image description here.Has anyone encountered a similar problem, is it caused by the alwayson cluster?

i think Log backup after the full backup has the first_LSN value equals to last_lsn of the last log backup. It shows that log backup does not break the LSN chain and it continues to maintain the chain since the last log backup. but in my chain log backup's first_LSN value not equals to last_lsn of the last log backup. And I found that for several relatively small database , the first_lsn of the current log backup is always 100000 less than the last_lsn of the previous log backup. There are no other scheduled jobs in mysql database. Also the differential backup as well

enter image description here

Improve this question
6
  • 1
    It would be helpful if you could highlight the row which you think is broken in the Log chain. I don't think a broken backup Log chain is something that should normally be caused by an AlwaysOn Availability Group (but idk enough to say it's not possible). Side note, your backup process does scare me a little with the lack of frequency of Full backups. How big is the database?...maybe you can increase the frequency you take Full backups, which could coincidentally minimize the chances of having a broken Log chain.
    – J.D.
    Commented Jul 14, 2022 at 12:00
  • 1
    Most common cause of this in AlwaysOn is taking log backups on multiple nodes, or writing log backups to local storage during a failover. See learn.microsoft.com/en-us/sql/database-engine/…
    – David Browne - Microsoft
    Commented Jul 14, 2022 at 22:22
  • @J.D. - note that given a good log chain, if a Diff is screwed up, you can go Full-log-log-log, and if a log is destroyed, you can skip over it with a later Diff, and go Full-Diff-log-log. If a Full is destroyed, you can even go to the prior Full and do Full-log-log-log-...-log-log.
    – Anti-weakpasswords
    Commented Nov 21, 2022 at 5:07
  • @Anti-weakpasswords Sure, but it'll take that much longer to do a restore. The scarier part is, wondering what is the backup retention policy when OP's Full backups are monthly. If OP doesn't have the Full backup from 2 months ago, with all of the Differential and/or Transaction Log backups, or if any of those are corrupt, then they're essentially relying on a single Full backup from 1 month ago. Hopefully it itself and none of the past month of Differential / Transaction Log backups are corrupt either.
    – J.D.
    Commented Nov 21, 2022 at 13:36
  • @J.D. In the case you HAVE to start skipping backups because they're screwed up, I'd argue that any restore - however long it takes - is superior to no restore. Your generic solution there is put backups on more reliable systems, and do more frequent full and diff backups, which has costs of its own, especially on VLDBs. Agreed that OP's backup frequency is problematic - I would argue for Fulls at a rate that let you always - even when a backup is in progress - have AT LEAST two (better 5) good fulls ready to restore, plus diffs, plus Tlogs.
    – Anti-weakpasswords
    Commented Nov 21, 2022 at 14:17

1 Answer 1

Reset to default
0

Daily log backups seems to be way, way, way too much time between each. Also, as the comments mentioned, I would be quite sure that some other backups are being taken not listed here.

  • Forget msdb data for restore scenarios. If your servers catch on fire, you won't have it anyway, so if you get used to not using it, you'll have better habits in a disaster, which means less stress.
  • So, look at your actual files - where do you back up to? Look there!
    • Are there more than one file a day there?
    • Do RESTORE HEADERONLY FROM DISK and look at the LSN's there. Do they match?
    • Acid test: do a test restore. Does it complain about a broken log chain?
  • As the comments mentioned, look at where every other node in the cluster might be backing up to also
  • If you still can't find it and still have log chain breakage, crank up the frequency of the log backups - I've seen one minute, 5 min, 15 min, 1 hr, 4 hr as pretty normal.
    • Let it run for a day; now, WHEN is the log breakage? You can narrow down from here. Note that you can have two schedules for the log backup job - one at "normal" frequency all day, and one at "high" frequency during the time-span you're narrowing in on.
      • Heads up - in at least older SQL, you COULD take a log backup with First LSN = Last LSN, and that errors out trying to restore. Skip it and move on, it's annoying but harmless.
  • All that failing... is someone doing a truncate log on you? Changing out of Full recovery model and back? Stop them.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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