FIX: SQL Writer Service can cause undetected deadlocks on system DMV when you do a VSS backup

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

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 andindefinitely keep locks against an internal system table in master (sys.sysdbregwith object_id 28). Moreover, these locks in turn prevent reading fromsystem Dynamic Management Views (DMV) like sys.dm_hadr_database_replica_states, and may lead to majorsecondary blocking in the instance. In addition, you notice that the block chain analysis returns the following statementoriginating 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) frommaster.sys.databases”

Then SQL Server will have a wait status of ASYNC_NETWORK_IO.

Note Because the ASYNC_NETWORK_IO wait is implemented, thewait_time against it will never exceed two seconds (2,000 ms). However, the wait typewill 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 DBSTARTUPSPID (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.