2

We have a four node Availability Group, two nodes in one site, two nodes off site in another data center. I have noticed after every WAN issue where the WAN connection is flapping, and the off site nodes constantly disconnect and reconnect (using AOAG health from the AOAG dashboard), the memory of the primary server gets consumed by the "HADR Log Block Msg Pool"

SELECT  *
FROM    sys.dm_os_memory_clerks
ORDER   BY pages_kb DESC

type: OBJECTSTORE_SERVICE_BROKER
name: HADR Log Block Msg Pool

In the worst case, when the network was flapping for hours, this memory clerk will end up consuming over 90% of the memory of the SQL Server causing the SQL Server to stop functioning (SQL has 10GB of memory "HADR Log Block Msg Pool" was using 9.8GB).

Is there any way to dump this HADR Log Block Msg Pool? Or stop it from growing so large in the first place? Our only solution so far has been to failover and restart the box.

There are no errors, just the logs for the node disconnects and reconnects and logs for the DBs re-hardening after the reconnects.

As more and more memory gets eaten by the "HADR Log Block Msg Pool" the memory available for everything else drops, affecting performance. Normally this 10GB of RAM is fine for this AOAG group and usage. It's only when the WAN flaps for a while that we have this issue.

We could throw more memory at the server, but I don't think that will solve the underlying problem, it would just buy us more time before it severely hurts performance.

I agree the network is the root cause, but it seems strange that after the issue is resolved and the AOAG is back in sync that SQL would not recover/reallocate RAM back to other SQL memory clerks like most SQL memory clerks do.

Log shipping won't work; it is a transactional environment, we need near real-time, preferably real-time, offsite DR. The AOAG group works 99% of the time and is almost always real time in sync. We are trying to work with the network team to improve connectivity, and/or maybe make it so it would just disconnect instead of flapping.

System Info
SQL version: SQL 2016 SP1 CU6 13.0.4457.0
OS version: Windows 2012 R2 6.3.9600
Server MEM: 12GB
SQL Max MEM: 10GB

Availability Group config info
Four databases are in the AOAG
The AOAG databases all together are 364GB
The two local nodes are in sync mode with one vote each
The two remote nodes are in async mode with zero votes
There is also a local file witness with one vote.

7

I have noticed after every WAN issue where the WAN connection is flapping, and the off site nodes constantly disconnect and reconnect, the memory of the primary server gets consumed by the "HADR Log Block Msg Pool"

Yes, this is currently by design. It is expected that the network between the two sites can handle the traffic and is available. Since it seems that's not the case, SQL Server really isn't the problem here but is manifesting as an issue. If you're going to continue to work over an unreliable and possibly extremely high latency low bandwidth connection then I wouldn't use availability groups. In fact, I'm not sure what you'd want to use as nothing would have a solid and reliable connection which seems to be the root cause of the issue.

Is there any way to dump this HADR Log Block Msg Pool?

Inside of SQL Server? No.

Or stop it from growing so large in the first place?

Yes, fix the connectivity issue and it won't grow. If it's prolonged connectivity issues, then remove the remote replicas from the AG and it'll stop growing. Since there are two remote replicas, the data will be sent twice which could be exacerbating the issue as it might not have been taken into account for the infrastructure available when architected.

Server MEM: 12GB

This is an awful small amount of server memory for 364 GB of databases + OS + Cluster + AG + all of the antivirus and agents installed.

  • I agree the network is the root cause, but it seems strange that after the issue is resolved and the AOAG is back in sync that SQL would not recover/reallocate RAM back to other SQL memory clerks like most SQL memory clerks do. Log shipping won't work it is a transactional environment, we need near real-time, preferably real-time, offsite DR. The AOAG group works 99% of the time and is almost always real time in sync. We are trying to work with the network team to improve connectivity, and/or maybe make it so it would just disconnect instead of flapping. – JasonBluefire Jan 17 at 4:38
  • We are planning on adding more memory to the cluster nodes to see if it helps, and I am trying to push to get the server updated to the latest CU. If both of those don't work I think we will be opening a case with Microsoft. – JasonBluefire Jan 17 at 4:45
  • 1
    @JasonBluefire The behavior will be the same with more memory but you can sustain longer periods of interruption before the memory pressure hits you again. – Sean Gallardy Jan 17 at 11:26
  • Networking re balanced traffic priority during a link failure/failover. We have only seen the issue once since then, (lost many WAN links during a complete fail of one of our providers) We did give all nodes more memory, doubling it. This did buy enough time that we did not see any averse affects of the HADR Log Block Msg Pool growing before enough of the WAN links were reestablished to regain stable a-sync connections. We still had to fail-over and restart the primary to release the Msg Pool memory. – JasonBluefire Apr 21 at 4:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.