AlwaysON : Log_Send_Queue_Size is increasing but the log_send_rate keeps decreasing?

Question

• Why, despite the increasingly log_send_queue_size for these two databases, did the log_send_rate keep decreasing?

• There were no bandwidth issues on the network at this time, and no other databases experienced this issue. If this happens again, is there a recommended fix, apart from having to manually restore the primary database over the secondary to re-synchronize the pair?

Environment :


SQL 2012, SP1 CU7 (Build 3393)


Windows Server 2012 Standard (Build 9200)


Availability Group of 10 databases (PRDDB1-AG1)


2 AG replicas, one in London and one in New York (LDSERVER1 & NYSERVER1), primary in NY, secondary in London.


2 databases within AG1, E-DB1 (50GB log file) and T-DB2 (250GB log file)

The T-DB2 database imports files from clients, processes them (lots of log activity) and then outputs to/updates data in the E-DB1 database.

This process generates lots of data churn and log activity against both databases.
We have occasional spikes of latency between London and New York database replicas maybe once or twice a week maximum but these always clear within a few hours.

Issue :

Last week we saw an increasing log_send_queue_size, and a decreasing log_send_rate. This started on the Monday, and carried on through to Friday night, when it was manually resolved (see Fix section below).
At its lowest, the log_send_rate of the E-DB1 database was just over 100KB/sec with a log_send_queue of over 40GB.
The T-DB2 database had a log_send_rate of 2000KB/sec shrinking down to 300KB/sec, with a log_send_queue of over 300GB.

This led to an increasing amount of latency between the primary and secondary replicas for these two databases within the availability group.
This was characterised by a build-up of log activity within the transaction log for each database affected, which is expected.
Because of this latency, the logs of each database affected expanded to the point where the log drive was in danger of running out of spac

Answering my own question, as future readers will benefit from it :

Seems like we might be hitting Longer latency for SQL Server 2012 database when you use Service Broker, database mirroring, and Availability Groups. This is fixed in SQL server 2012 SP2 CU1. The KB 2976982 has a typo(AlawysOn). So if you are searching by AlwaysON, it wont show up.

After the patch was applied, the issue was fixed.

StackExchange Database Administrators Q#82963, answer score: 3