Operational Defect Database
BugZero found this defect 1317 days ago.
Data sources
All data on this page is proprietary to BugZero® or gathered from public sources
Microsoft SQL Server | 12750851
KB4480652 - FIX: SQL Writer Service can cause undetected deadlocks on system DMV when you do a VSS backup - Microsoft Support
Last update date:
6/2/2022
Affected products:
SQL Server 2016 Developer - duplicate (do not use)
SQL Server 2016 Enterprise - duplicate (do not use)
SQL Server 2016 Enterprise Core - duplicate (do not use)
SQL Server 2016 Standard - duplicate (do not use)
SQL Server 2017 on Windows
Affected releases:
build lower than 14.0.3162.1
Fixed releases:
14.0.3162.1
Description:
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: Cumulative Update 15 for SQL Server 2017 Cumulative Update 6 for SQL Server 2016 SP2 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: Latest cumulative update for SQL Server 2017 Latest cumulative update for SQL Server 2016
References
Learn about the terminology that Microsoft uses to describe software updates.
