June 14, 2019 at 10:00 am
First of all - this is all OK now. I just want to know if I did anything wrong or what I could have done to glean more information from the system.
We had a comms 'hiccup' on one of our async servers (SQL 2012). Two databases that were synchronising were then in a 'restoring' state.
Attempting to add them back into the availability group informed me that the log file in one was not sufficiently up to date and in the other that there were transactions missing.
So, set HADR off for the two databases, restored the full backup taken from the sync server and then all of the log backups, which were taken from the primary (no recovery). They restored without issue.
Attempted to add the databases back into the AG and was informed that the logs weren't sufficiently up to date to allow that.
OK, so I performed log backups on primary and restored them onto these async databases. Same message - not sufficiently up to date.
Finally I ran a full backup (copy only) from primary, restored onto this async server and this time it all worked fine.
It doesn't appear to be a complicated process (first time I've had to do it in anger) but I obviously had an issue with restoring the db and then log files. There are of course good odds that I missed something obvious and I'm prepared to be told I did something stupid.
So, I have a handful of questions -
Why didn't the initial log file restores think they were up to date enough? It was all of the log file backups available.
Why didn't the log file backup I took then get it up-to-date sufficiently? It wasn't a large database and the backup/restore took under two minutes.
What queries could I have used to get more information about why it wasn't happy, after the restores?
Thanks for any information that enlightens myself (or others) in this area.
June 15, 2019 at 10:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 15, 2019 at 4:54 pm
Without seeing everything that occurred - it would be almost impossible to determine why the process failed the first/second time. With that said - it is possible that another tlog backup was taken and you did not have that backup restored when you attempted to add the database.
It is possible that you could have just restarted the synchronization process without having to restore again - but again, not possible to tell at this point. And - with smaller databases it is sometimes just easier to backup/restore and re-add the database.
I don't think you did anything wrong...
I have found that the backup - copy of the file - restore process often exceeds the frequency of the transaction log backups, which leads to the above issue. Depending on the database - I may suspend the tlog backups on the primary, copy the last transaction log backup and restore - add the database - then restart the tlog backups. This just insures that no new backups are executed during that process so I can be sure the destination is up to date.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply