SCCM failed with an inventory data import error issue

[#1]

Summary

This article covers a known issue that is encountered when importer fails with "cannot obtain LOCK resource" error when running reader 'DeleteFromImportedInstalledFileEvidence'

Symptoms

SCCM inventory import fails with the error:

2016-07-15 03:12:26,603 [ERROR]     System.Data.SqlClient.SqlException (0x80131904): The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
   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.ExecuteSQL(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:12b1b71f-4fd8-4c91-97bc-c135abdbcdd2
Error Number:1204,State:4,Class:19

It looks like the 'Import into Staging' has failed.

Cause

The importer is consuming a large amount of memory, which is not released frequently enough to meet the importer's demands. The cause of this memory consumption is the way that we batch delete records from the ImportedInstalledFileEvidence table.

Resolution

This is a known issue tracked under FNMS-38466. This has been fixed in FNMS 2016 R1 SP1.