AlwaysOn transaction error 9002: the transaction log for database '' is full due to 'AVAILABILITY_REPLICA'

  • chrisph (9/24/2015)


    @Jackin, how do you force a checkpoint?

    execute the following query in your chosen database

    Checkpoint

    chrisph (9/24/2015)


    @Perry, 4 databases, one large one is causing the errors.

    Full and log backups are set to primary, nightly fulls, 30 min tran logs.

    SQL2014 Enterprise SP1.

    So, backups only on the primary then?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • The backup preference is set to primary, so whatever node is primary will run the jobs. This is also so that true full backups can be taken, can only do copy-only backups on a secondary node. I've tested that successfully with manual failovers, though the full backups will fail if a database has the log full due to 'availability replica'.

  • Chrisp, Were you able to resolve the problem? Did any of the above work?

  • chrisph (9/24/2015)


    @Jackin, how do you force a checkpoint?

    @perry, 4 databases, one large one is causing the errors.

    Full and log backups are set to primary, nightly fulls, 30 min tran logs.

    SQL2014 Enterprise SP1.

    I asked how many secondary replicas not how many were in the AO group, how many secondarys do you have?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Has this been resolved? I am having same issue and I stumbled to this thread.

    Same exact issue: LOG and BACKUPS occurring only on PRIMARY. 4 replica members.

    Log_reuse_wait_desc is "AVAILABILITY_REPLICA" on some databases.

    Is there a resolution other than remove and re-add to AG?

    Thanks!

  • We have the exact same issue.

    Interestingly, due to hardware failure, our AG currently consists of only one (1) Replica, so there cannot be any issue with synchronization.

    But we also had this issue before, when everything was normal.

    The query from above states that log_send_queue_size and redo_queue_size are (as is expected) NULL.

    The only solution seems to be to remove the database from the AG.

  • We also have the exact same issue.

    We opened a case. MS says this is a known issue:

    https://support.microsoft.com/en-us/kb/3095156

    The issue was first fixed in the following cumulative update of SQL Server:

    Cumulative Update 3 for SQL Server 2014 SP1

    So we will install CU3 and hope the error will be fixed!

    Edit: Since MS now recommends to install the LATEST CU, we will install CU6 (KB3167392).

Viewing 7 posts - 16 through 22 (of 22 total)

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