BizTalk Job 'TrackedMessages_Copy_BizTalkMsgBoxDb' fails with deadlock message

ISSUE

===================

BizTalk Job 'TrackedMessages_Copy_BizTalkMsgBoxDb' fails and we see in the job history of this job

deadlock as below:

Executed as user:PKUBL\clusteradmin.Transaction(Process ID:224) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.Rerun the transaction[SQLSTate 40001][error 1205]

Warining:The join order has been enforced because a local join hint is used[SQLSTATE 010000][Error 8625].The step failed.

In the MBV we see the below:

 CRITICAL WARNINGS

In the non critical warning section in MBV, we see the below:

MsgBody Tracking for some ReceivePorts Yes (MsgBody can accumulate in MsgBox db if 'TrackedMessages_Copy' job is not running to send them to DTA) !

CAUSE

=========

We had the message body tracking enabled for some of the receive locations and those receive locations were having huge load in peak times.

So this was causing the job 'TrackedMessages_Copy_BizTalkMsgBoxDb' to fail with deadlock error when these receive locations with message body tracking enabled were having huge load.

We had two receive locations receiving messages under the receive port ReceivePort_KeepAlive. The ReceivePort_KeepAlive had tracking enabled for message bodies before the port processing and after port processing. This was a two way port and this means that for each receive, there are 4 tracked message bodies for each inbound message.

This was a high volume environment, if they were to receive 250,000 messages/day on these receive locations, they would be tracking 1 million message bodies.

RESOLUTION

===========

The issue was resolved by disabling the message body tracking for the receive port.

Enabling Message body tracking for many ports might cause deadlock for the job TrackedMessages_Copy_BizTalkMsgBoxDb.This is by design in BizTalk.