SQLTeam.com | Weblogs | Forums

Adding db to alwayson setup

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.:frowning:

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

Delete the secondary db's and proceed with add database to AG.

This post was flagged by the community and is temporarily hidden.