Reconcile import fails with"System.Data.SqlClient.SqlException (0x80131904): Timeout expired" error in processing the DeleteFromImportedInstallerFileEvidence of SMS reader

[#1]

Summary

DeleteFromImportedInstallerFileEvidence taking a long time. Reconcile import fails with"System.Data.SqlClient.SqlException (0x80131904): Timeout expired" error in processing the DeleteFromImportedInstallerFileEvidence of SMS reader.

Symptoms

For FlexNet Manager Suite version before 2017, User may encounter a reconcile failure with following error message.

Inventory source: Microsoft System Center Configuration Manager (SCCM)

Reconcile import fails with like the following error.

2017-04-03 11:20:57,882 [INFO ]     DeleteFromImportedInstalledFileEvidence (Execute on FNMP)
2017-04-03 19:20:58,252 [INFO ]       Failed to execute Reader 'DeleteFromImportedInstalledFileEvidence' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Reader\sms\FileEvidence.xml
Error: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
2017-04-03 19:20:58,252 [INFO ]       All retries have been attempted for Reader 'DeleteFromImportedInstalledFileEvidence'
2017-04-03 19:20:58,252 [INFO ]       Completed with error in 8 hours, 0 minutes, 0 seconds.
2017-04-03 19:20:58,252 [INFO ]     Released application lock Reader_2260db06-316d-486b-95a8-9fa8a638b100_Evidence_ALL
2017-04-03 19:20:58,252 [ERROR]     System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at ManageSoft.Compliance.Importer.Logic.XML.ExecuteOnTarget.ProcessStep(IExecutionContext context)
   at ManageSoft.Compliance.Importer.Logic.XML.Reader.Execute(IExecutionContext context)
   at ManageSoft.Compliance.Importer.Logic.ActionExecuter.ReaderExecuter.ExecuteSingleReader(Reader reader, Int32 procedureOrder, Version sourceDatabaseVersion)
   at ManageSoft.Compliance.Importer.Logic.ActionExecuter.ReaderExecuter.Execute()
   at ManageSoft.Compliance.Importer.Logic.ComplianceImporter.ProcessExecution(ComplianceReader p_ComplianceReader, Tenant p_Tenant, IExecutionContext p_Context)
ClientConnectionId:2fa7c1a6-18de-4a13-9e7d-c7108507a8e0
Error Number:-2,State:0,Class:11
2017-04-03 19:20:58,799 [INFO ]   Released application lock ManageSoftComplianceImporter_Exclusive
2017-04-03 19:20:58,815 [INFO ]   Time: Monday, April 3, 2017 7:20:58 PM
2017-04-03 19:20:58,815 [INFO ]   Total import time: 8 hours, 5 minutes, 13 seconds
2017-04-03 19:20:58,815 [INFO ]   12 source data warnings
2017-04-03 19:20:58,815 [INFO ]   1 errors, 0 warnings

Cause

An SQL script in the 'DeleteFromImportedInstalledFileEvidence' of "C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Reader\sms\FileEvidence.xml" consumes a lot of cost due to missing index on the MachineID column of #SMSComputer table.

Resolution

"C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Reader\sms\Computer.xml" in FlexNet Manager Suite 2017 R1 has been updated to add a primary key on the MachineID column of #SMSComputer table.

Workaround

1. Open "C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Reader\sms\Computer.xml" file via text editor.
2. Search the following section.

	IF OBJECT_ID('tempdb..#SMSComputer') IS NOT NULL
		DROP TABLE #SMSComputer
	CREATE TABLE #SMSComputer
	(
		MachineID int,
		ComputerName nvarchar(256) COLLATE database_default,
		Domain nvarchar(100) COLLATE database_default,
		Manufacturer nvarchar(128) COLLATE database_default,
		ModelNo nvarchar(128) COLLATE database_default, 
		OperatingSystem nvarchar(128) COLLATE database_default,
		ServicePack nvarchar(128) COLLATE database_default,
		NumberOfProcessors int,
		TotalMemory bigint,
		LastLoggedOnUser nvarchar(128) COLLATE database_default,
		InventoryDate DateTime,
		InventoryAgent nvarchar(64) COLLATE database_default,
		CalculatedUser nvarchar(128) COLLATE database_default
	)

3. Update the following line.
   • Before

		MachineID int,

• After

		MachineID int PRIMARY KEY,

4. Save the file.

Additional Information

JIRA #FNMS-44584
In 2017 R1 we have already added a primary key on that column therefore the issue is fixed after the version of 2017