Deadlocks in MR Data Integration EventLog

Hi,

Since few days I am getting error in Event Viewer regarding MR. It is coming in between successful command as below in MR Data Integration EventLog

completed: 3 record(s) have been written. 0 record(s) have been deleted. 0 record(s) have failed. 0 record(s) were filtered. Total runtime was 9 seconds.

Error as follows :

:System.AggregateException: One or more errors occurred. —> System.Data.SqlClient.SqlException: Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.b__0()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Microsoft.Dynamics.Performance.Core.SqlCommandWrapper.RunCommand[TResult](SqlCommand command, Func1 execute)
at Microsoft.Dynamics.Performance.DDM.SqlUtility.ExecuteSql(String sql)
at System.Threading.Tasks.Parallel.<>c__DisplayClass17_01.<ForWorker>b__1() at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask) at System.Threading.Tasks.Task.<>c__DisplayClass176_0.<ExecuteSelfReplicating>b__0(Object ) --- End of inner exception stack trace --- at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Func4 bodyWithLocal, Func1 localInit, Action1 localFinally)
at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IEnumerable1 source, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Action3 bodyWithStateAndIndex, Func4 bodyWithStateAndLocal, Func5 bodyWithEverything, Func1 localInit, Action1 localFinally)
at System.Threading.Tasks.Parallel.ForEach[TSource](IEnumerable1 source, Action1 body)
at Microsoft.Dynamics.Performance.DDM.SqlUtility.ExecuteSqlInParallel(IEnumerable1 parallelSql) at Microsoft.Dynamics.Performance.DDM.FactLoader.ProcessStagedData(String connectionString) at Microsoft.Dynamics.Integration.Adapters.DimensionalDataMart.DataMartAdapter.RunMaintenance() at Microsoft.Dynamics.Integration.Service.Tasks.MaintenanceTask.RunImplementation() ---> (Inner Exception #0) System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func)
at Microsoft.Dynamics.Performance.Core.SqlCommandWrapper.RunCommand[TResult](SqlCommand command, Func1 execute) at Microsoft.Dynamics.Performance.DDM.SqlUtility.ExecuteSql(String sql) at System.Threading.Tasks.Parallel.<>c__DisplayClass17_01.b__1()
at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask)
at System.Threading.Tasks.Task.<>c__DisplayClass176_0.b__0(Object )
ClientConnectionId:d258c278-49fe-48ec-9cfe-51c40ca82dd5
Error Number:1205,State:18,Class:13<—

I suggest you install the latest version - it’s possible that this problem has already been fixed.

If not, logging details about deadlocks (in SQL Server) will give you a better idea about what’s wrong (which queries locked which resources etc.).