October 5, 2019 at 2:52 pm
Hi,
we configured alwayson with 15 dbs in sql 2014. we configured full,diff and log backups. due to some reason the yesterday all log backups failed. due to this the logs grown hugely. i fixed the log backup drive.after taking continuous log backups and shrinking the log files three of the dbs logs were huge still. after trying many times as log space is not reducing i removed the dbs from always and made the recovery model simple and tried shrinking the log files. After this log space reduced and L drive came back to normal. Till here everything fine. again i made the dbs recovery mode to full.
To add those two dbs back in alwayson setup took full backup and log backup freshly in primary replica.And restored them with no recovery mode in Secondary and DR Replica. When i tried to add the dbs in always everything was success in the validation but at the end i got a below error.
------------------------
Failed to join the database 'xyz' to the availability group 'ABCSQL2014' on the availability replica 'Replica2'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.5000.0+((SQL14_PCU_main).160617-1804)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The mirror database, "xyz", 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)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5223&EvtSrc=MSSQLServer&EvtID=1478&LinkId=20476
------------------------------
I tried in both ways for adding dbs in always back.
1. I restored freshly fulL backup and t-Log backup with no recovery mode in 2ndary and DR Node. while adding dbs in alwayson selected the "Join" option in Data synchronization. -got error.
2. without restoring dbs in secondary and dr node i selected "Full" option in Data synchronization by giving the network backup folder-Still got the error.:(
I tried creating the test db with one table. with above two trails i am able to add db in two replicas without errors. But why i am not able to add those dbs which i removed yesterday and add them back now?? i already restored log backup with no recovery mode. then why it is showing still needs to restore t-log backup in secondary?
Am i doing anything wrong? These are production dbs. Please help me to add them back in Always on setup..
Thanks
Jo
October 5, 2019 at 3:37 pm
Did you stop the transaction log backups on the primary for those 2 databases? If not - most likely one or more transaction log backups have occurred since you started the restore process on the secondary.
In order to be able to add the database - it must be at the same point as the primary and that means any new transaction log backups that have been taken must be restored also.
One way to avoid that is to stop the transaction log backups on the primary until after you have restored on the secondary - and joined the database to the availability group.
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
October 6, 2019 at 4:17 am
Thanks allot. Around 2 hrs i wasted my time on this. i missed this logic.. Yes i removed the dbs from t-log backup job then added the dbs in alwayson. It worked.. 🙂 Thank you so much..
October 6, 2019 at 4:10 pm
You are welcome
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