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