"Execution Timeout Expired" error with FlexNet Manager Suite databases hosted on SQL Server 2016 SP2 CU16 (13.0.5882.1)

[#1]

Symptoms

When using SQL Server 2016 SP2 CU16 (13.0.5882.1) to host the FNMS databases, you may start to see inventory import and license reconcile tasks failing with the error Execution Timeout Expired. This will always timeout regardless of the timeout limit set in the environment, and you will see errors like the following in the importer.log from the inventory import and license reconcile:

Failed to execute Writer 'WriteComputers - set calculated user' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\Computer.xml, at step line 1
Error: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Failed to execute Writer 'MatchAccessingDevice' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\AccessEvidence.xml, at step line 1
Error: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Updating License compliance status based on new install counts.
2021-03-30 03:12:41,043 [ERROR]       System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out

Diagnosis:

This is caused by a bug in SQL Server 2016 SP2 CU16 (13.0.5882.1), that has been addressed in SQL Server 2016 SP2 CU17 (13.0.5888.11)

Solution:

To resolve this issue, you will need to apply CU17: KB5001092 - Cumulative Update 17 for SQL Server 2016 SP2 

[dbeckner]

We are seeing the exact symptoms above on SQL 2016 Version 13.0.5026.0. Does this issue extend to other versions prior to the CU17 patch? Our imports have had no issues until about 2 weeks ago when reconciliation began timing out. We extended the length of the timeout threshold to 8 hours but it still will not complete. Trying to determine if patching the servers will resolve the issue.

[ChrisG]

From a quick search I haven't found anybody else who has experienced this particular issue with anything but CU16, but that doesn't mean updating your SQL Server installation that uses an earlier CU won't address the problem you're experiencing. If you do try updating SQL Server, it would be great to hear whether or not it helps with your problem.

[bwcbwc59]

We are seeing this appear after upgrading from SQL Server 13.0.5426.0 to SQL Server 13.0.5850.14. The underlying query that fails is 
UPDATE dbo.AccessingDevice
SET ComplianceComputerID = cc.ComplianceComputerID
FROM dbo.AccessingDevice ad
CROSS APPLY (
SELECT TOP 1 cc.ComplianceComputerID
FROM dbo.ImportedAccessingDevice iad
INNER JOIN dbo.ImportedComputer ic
ON iad.ExternalAccessingDeviceID = ic.ExternalID
AND iad.ComputerName = ic.ComputerName
AND iad.ComplianceConnectionID = ic.ComplianceConnectionID
INNER JOIN dbo.ComplianceComputerWithActiveInventory cc
ON ic.ComplianceComputerID = cc.ComplianceComputerID
WHERE iad.AccessingDeviceID = ad.AccessingDeviceID
ORDER BY cc.InventoryDate DESC
)cc
WHERE ad.ComplianceComputerID IS NULL

which is getting stuck in a CXCONSUMER wait-state in the DB server.  It's not clear what change in CU17 applies to this.

[WheresThePizza]

@bwcbwc59 

The change this applies to is for issue 13685819. Before CU17, especially when upgrading to CU16, there was an issue where data manipulation queries would get into a state where they would not complete in a timely manner or not complete at all. This would cause FNMS to potentially fail at various points, depending on what was being queried at that time. 

https://support.microsoft.com/en-us/topic/kb5001092-cumulative-update-17-for-sql-server-2016-sp2-5876a4d6-59ac-484a-93dc-4be456cd87d1#bkmk_13685819

[bwcbwc59]

@WheresThePizza  Thanks. Also, after further validation of the environment, we are actually on the 13.0.5882 build, so no discrepancy in the KB article. We are reverting to CU15 as CU 17 isn't currently available in RDS