Symptoms

Assume that you use an Always On Availability Group that has two secondary replicas configured for synchronous-commit mode. When you change the availability mode for one of the secondary replicas from synchronous-commit mode to asynchronous-commit mode, the synchronization state of the availability database on that secondary replica may still be reported as SYNCHRONIZED instead of SYNCHRONIZING on the primary replica. This causes transaction delays on the primary replica.

Cause

This problem occurs because the primary replica waits for the progress message from both secondary replicas to update the commit LSN. This occurs even though one of the secondary replicas is configured for asynchronous-commit mode. Because the asynchronous-commit replica sends the progress message infrequently, it delays the update of the commit LSN on the primary replica. This, in turn, delays updating user transactions.

Resolution

This problem is fixed in the following updates for SQL Server:

          Cumulative Update 10 for SQL Server 2017

          Cumulative Update 3 for SQL Server 2016 Service Pack 2

Cumulative Update 10 for SQL Server 2016 Service Pack 1

About SQL Server builds

Each new build for SQL Server contains all the hotfixes and security fixes that were in the previous build. We recommend that you install the latest build for your version of SQL Server:

          Latest cumulative update for SQL Server 2017

The latest build for SQL Server 2016

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

Learn about the terminology Microsoft uses to describe software updates.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×