Symptoms
Consider the following scenario:
-
You use Microsoft SQL Server 2016 Service Pack 2 (SP2) Cumulative Update 2 (CU2) or later versions.
-
You are running an instance that hosts many databases (typically more than 200), and the instance is a part of an Always On Availability Group (AG) (typically with the role of secondary server). Note All user databases do not need to belong to AGs.
-
You run a Volume Shadow Copy Services (VSS) application to request a backup of all or some databases of the SQL Server instance.
In this scenario, the SQL Writer Service can reach a deadlock situation and indefinitely keep locks against an internal system table in master (sys.sysdbreg with object_id 28). Moreover, these locks in turn prevent reading from system Dynamic Management Views (DMV) like sys.dm_hadr_database_replica_states, and may lead to major secondary blocking in the instance. In addition, you notice that the block chain analysis returns the following statement originating from the SQL Server VSS Writer as a head blocker:
“select name, recovery_model_desc, state_desc, CONVERT(integer, is_in_standby), ISNULL(source_database_id,0) from master.sys.databases”
Then SQL Server will have a wait status of ASYNC_NETWORK_IO.
Note Because the ASYNC_NETWORK_IO wait is implemented, the wait_time against it will never exceed two seconds (2,000 ms). However, the wait type will remain in place indefinitely, and this situation will never unblock on its own. It requires you to kill the SQL Writer Service to unblock the situation.
Among SPID blocked by this head blocker, there will often be a DBSTARTUP SPID (alwayson Secondary DB redo).
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Resolution
This issue is fixed in the following cumulative updates for SQL Server:
About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
References
Learn about the terminology that Microsoft uses to describe software updates.
