February 5, 2019 at 7:23 am
Twice now I have received an error joining a database to an AG. I take a full backup and a log backup on the primary, I restore those with no recovery on the secondary. I run the wizard from SSMS to join the database to the AG and it fails. I get "The mirror database, 'MyDB', has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)". I've verified twice now that I'm using the most recent full and the associated log backups that follow. I don't understand what the issue is.
After this initial post, I tried to let SQL Server take the backups and it fails saying "Cannot drop database "BackupLocDb_0f365a1f-d9af-4bfc-9c67-53ae1bda93f5" because it is currently in use. (Microsoft SQL Server, Error: 3702)"
This happens during the part where it does a check that it can access the shared network location. This process creates that file, but it can't drop the file because it's in use?
February 5, 2019 at 9:08 am
The first issue can arise if you allow connections to the database on the primary replica before you have successfully added it to the AG. It can be a bit of a race against time, because you don't just need the most recent log backup, you need one taken extremely recently, if you see what I mean? The easiest thing to do might be to deny connections to that db, then take one final log backup. Restore your original full and log backups plus that extra log backup you just took, and it *should* work.
February 5, 2019 at 9:15 am
Beatrix Kiddo - Tuesday, February 5, 2019 9:08 AMThe first issue can arise if you allow connections to the database on the primary replica before you have successfully added it to the AG. It can be a bit of a race against time, because you don't just need the most recent log backup, you need one taken extremely recently, if you see what I mean? The easiest thing to do might be to deny connections to that db, then take one final log backup. Restore your original full and log backups plus that extra log backup you just took, and it *should* work.
This is a QA environment. There aren't currently any transactions even happening in the databases I'm trying to join to the AG. When I script out what the GUI is doing it's the ALTER DATABASE '' SET HADR AVAILABILITY GROUP = MyAG; running on the secondary that is failing.
February 5, 2019 at 3:59 pm
lmarkum - Tuesday, February 5, 2019 9:15 AMBeatrix Kiddo - Tuesday, February 5, 2019 9:08 AMThe first issue can arise if you allow connections to the database on the primary replica before you have successfully added it to the AG. It can be a bit of a race against time, because you don't just need the most recent log backup, you need one taken extremely recently, if you see what I mean? The easiest thing to do might be to deny connections to that db, then take one final log backup. Restore your original full and log backups plus that extra log backup you just took, and it *should* work.This is a QA environment. There aren't currently any transactions even happening in the databases I'm trying to join to the AG. When I script out what the GUI is doing it's the ALTER DATABASE '' SET HADR AVAILABILITY GROUP = MyAG; running on the secondary that is failing.
If another transaction log backup is taken after the transaction log backup you have taken for this process - you will get this error. Make sure you stop the job that is performing transaction log backups until after you have restored the backup and transaction log backup, or make sure you apply all transaction log backups from the primary up to the latest available.
For example - if it takes 15 minutes to restore the database and another 5 minutes to restore the transaction log backup, and your scheduled transaction log backups are running every 10 minutes you will have at least 2 additional transaction log backups taken since you started the process. You also have to consider how long it took to copy the backup files...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 7, 2019 at 12:00 pm
Jeffrey Williams 3188 - Tuesday, February 5, 2019 3:59 PMlmarkum - Tuesday, February 5, 2019 9:15 AMBeatrix Kiddo - Tuesday, February 5, 2019 9:08 AMThe first issue can arise if you allow connections to the database on the primary replica before you have successfully added it to the AG. It can be a bit of a race against time, because you don't just need the most recent log backup, you need one taken extremely recently, if you see what I mean? The easiest thing to do might be to deny connections to that db, then take one final log backup. Restore your original full and log backups plus that extra log backup you just took, and it *should* work.This is a QA environment. There aren't currently any transactions even happening in the databases I'm trying to join to the AG. When I script out what the GUI is doing it's the ALTER DATABASE '' SET HADR AVAILABILITY GROUP = MyAG; running on the secondary that is failing.
If another transaction log backup is taken after the transaction log backup you have taken for this process - you will get this error. Make sure you stop the job that is performing transaction log backups until after you have restored the backup and transaction log backup, or make sure you apply all transaction log backups from the primary up to the latest available.
For example - if it takes 15 minutes to restore the database and another 5 minutes to restore the transaction log backup, and your scheduled transaction log backups are running every 10 minutes you will have at least 2 additional transaction log backups taken since you started the process. You also have to consider how long it took to copy the backup files...
Thanks Jeffrey. I handed this off to our 3rd party DBA Service company so I could do some other stuff and once they got it working that is essentially the answer they gave me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply