This used to be a very hot topic around SQL Server 2016 when automatic seeding first came out, then everyone learned how automatic seeding was just manual seeding but done for you and the topic went away. A decade later, it’s somehow back to being a hot topic. I guess all that is old is new again.
Error 1408 states that the database doesn’t have enough log to find a starting point with the primary/principal – which, let’s just use AG terminology from this point on because mirroring has been dead for 14 years and counting.
The remote copy of database “%.*ls” is not recovered far enough to enable database mirroring or to join it to the availability group. You need to apply missing log records to the remote database by restoring the current log backups from the principal/primary database.
But Sean, it says AUTOMATIC SEEDING, so this should all be done AUTOMATICALLY. Well, sure, it is but that doesn’t mean it has any more or less magic than doing it the normal way. In fact, automatic seeding is just doing all of these things for you, automatically, as the name states. If we take a look at MANUAL seeding we can see the steps (and notes/warnings), where there’s a nice recommendation section.
Before preparing your secondary databases, we strongly recommend that you suspend scheduled log backups on the databases in the availability group until the initialization of secondary replicas has completed.
The only difference between automatic and manual seeding is who does the work. In manual, it’s you (or some automation scripts you have), in automatic it’s SQL Server. The steps are the same, the notes are the same, the warnings and guidelines are all the same, with the added warning that automatic seeding is going to take a net new backup and stream it over versus being able to use a previous backup taken and if you have a large database you should manually seed it. Given this, you’d expect that if someone was automatically seeding a replica or set of replicas, they’d suspend their log backups. You’d be wrong.
If you take a log backup when automatic seeding is running – aka the backup is being taken and sent to the secondary replica that is being seeded – then log truncation can’t occur because the backup is already running. When that backup completes, log truncation can now occur, that’s why it’s stated to not run log backups during manual or automatic seeding. If you truncate the log before the replica is joined then you’re going to receive error 1408. It’s pretty straight forward and I’m honestly not sure how this is a huge point of confusion of contention. If you’ve never worked with AGs, never read the documentation, and let AI do it for you then I can absolutely understand how this is perceived as a huge issue and bug, but it’s not, it’s just lack of effort and understanding. I’m not expecting everyone to be experts in things from day 1, that’s preposterous, but the flip side is don’t be a fool.
Just because you’re using automatic seeding AND taking log backups doesn’t mean this will ALWAYS error with 1408, either. It’s entirely a timing item. If the join happens fast enough, it might join no problem. If the log backup hits and truncates right as the join comes in, then it’ll most likely result in an error.
Generally the follow up to answering the 1408 error questions I get results in questions of if automatic seeding should holdup truncation until it’s done, thus removing the 1408 timing condition. I’m a bit mixed on this, on one hand the experience should be solid, robust, and typically work in a way that if you gave someone who didn’t work with the item an explanation of it that the prevailing guess as to how it worked would be roughly how it actually works. On the other hand, I’ve witnesses some pretty crazy ways that people mess up their SQL Server instances and I’m not sure I’d want my log truncation held up for days because of it.