Why does the Reconcile time out and fill up transaction log after reducing the value of MaxDaysToKeepLicenseReconcileResults

[#1]

Summary

If you get an error similar to "The transaction log for database 'FNMSOperations' is full due to 'ACTIVE_TRANSACTION'." after reducing the MaxDaysToKeepLicenseReconcileResults then this article may help.

Question

After reducing the value in dbo.ComplianceSetting for "MaxDaysToKeepLicenseReconcileResults" you may find that the reconcile times out and gives an output similar to the one below:

[INFO ] Update software license snapshot purchase prices that are not set with the most recent linked purchase order price
[ERROR] System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: The transaction log for database 'FNMSOperations' is full due to 'ACTIVE_TRANSACTION'.
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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, 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.Database.DatabaseCommand.ExecuteNonQuery()
at ManageSoft.Database.Impl.DatabaseLayerBaseSql.Execute(CommandType p_CommandType, String p_CommandText, DatabaseParameter[] p_Parameters)
at ManageSoft.Compliance.Importer.Procedures.ManageSoft.LicenseReconcile.PerformLicenseReconcile(Int32 p_LicenseMeasurementID, Boolean p_isPartial)
at ManageSoft.Compliance.Importer.Procedures.ManageSoft.LicenseReconcile.ReconcileLicenses(Nullable`1 p_OverrideLicenseMeasurementID, String PublisherFilter)
at ManageSoft.Compliance.Importer.Procedures.ManageSoft.LicenseReconcile.ReconcileLicenses(String PublisherFilter)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
at ManageSoft.Compliance.Importer.Logic.XML.ProcedureItem.ExecuteAssembly()
at ManageSoft.Compliance.Importer.Logic.XML.Writer.Execute(IExecutionContext context)
at ManageSoft.Compliance.Importer.Logic.ComplianceImporter.ExecuteWriters(ComplianceReader p_ComplianceReader, IExecutionContext context, String singleConnectionIdentifier)


What is the cause and how can I resolve it?

Answer

The MaxDaysToKeepLicenseReconcileResults setting determines how long the reconcile will keep old reconcile results that have completed; any results that are older than the limit are deleted during the reconciliation using the DeleteOldLicenseReconcileResultsCleanup stored procedure.

The default is 60 days so if this is dropped significantly e.g. from 60 down to 10 then it leaves you with a few weeks worth of data to delete which if you have frequent reconciles running (such as PVU scanning ones) then this is a lot of data to delete.

You can manually delete the data using a script such as the one attached to this article as "OldReconcileResultsCleanup.sql" and after that the reconcile should complete successfully.