log backup job getting failed for particular DBs in secondary replica always on SQL

Question

log backup job getting failed for particular DBs in secondary replica always on SQL with below error.

Error :

Log backup for databases on a secondary replica failed because the last backup LSN from the primary database is greater than the current local redo LSN

Answer 1

Based on comments from above, this sounds like a race condition having to do with taking log backups on multiple replicas in your topology. For example, if you take a log backup on your primary replica that includes LSNs 1-15 (inclusive) and the secondary replica is redone only through LSN 12, any attempt to backup the log on the secondary replica doesn't have sufficiently new data to complete.

That said, there is an simple fix - set backup preferences. That by itself doesn't fix the issue as whatever backup procedure you're using needs to honor those settings (the default backup log command does not; backup database also does not, but that's not in scope for this discussion). This too is a simple fix - use Ola Hallengren's backup procedure. It does obey those settings. Making it explicit, because it does obey those settings you can leave the jobs enabled on all replicas at all times.

---

One other piece of advice - I'd run your log backups on the primary node only. Why? Let's say that there is some reason that your replica is significantly far behind. It can only back up log for LSNs that it has. Even in the best case scenario, this prevents log from clearing at the primary. But it also jeopardizes your target RPO. If disaster were to strike while the replica was behind, the closest you could get would be however far the replica doing the backups was to current. Imagine this conversation - "yeah boss... I know that we take log backups every five minutes, but the best recovery I can do is from two hours ago". Your primary replica is always current and so that lag doesn't happen.