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

Question

Post reply

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

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

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

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" 😉

chrisph Hall of Fame Points: 3701 More actions · Answer 2

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

JackinTBox Old Hand Points: 311 More actions · Answer 3

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

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" 😉

SQLSalas Ten Centuries Points: 1327 More actions · Answer 5

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!

OliverRahner SSC Rookie Points: 32 More actions · Answer 6

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.

babo Old Hand Points: 335 More actions · Answer 7

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