error while adding db back to always on

  • pols

    SSC Eights!

    Points: 861

    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

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • pols

    SSC Eights!

    Points: 861

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

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    You are welcome

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply