FIX: Transaction delays on the primary replica if database synchronization is reported incorrectly on a secondary replica in SQL Server

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core More

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

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.