FIX: A transaction is deadlocked and your job fails in BizTalk Server 2004

• An event that is similar to the following is logged in the Application log:

  Event Type: Warning
  Event Source: SQLSERVERAGENT
  Event Category: Job Engine
  Event ID: 208
  Date: Date
  Time: Time
  User: N/A
  Computer: ComputerName
  Description: SQL Server Scheduled Job 'MessageBox_Message_Cleanup_BizTalkMsgBoxDb' (0xA5E598C6D04620468E8DD11C98F6D544) - Status: Failed - Invoked on: Date and Time - Message: The job failed. The Job was invoked by Schedule 86 (Schedule). The last step to run was step 1 (Purge).

• If you view the Microsoft SQL Server 2000 Enterprise Manager job history, you notice information that is similar to the following:
  Executed as user: CONTOSO\username. Transaction (Process ID 112) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
• If you run SQL Server 2000 together with the -T1204 trace flag, you notice information that is similar to the following :

  Deadlock encountered .... Printing deadlock information
  2004-03-31 14:11:08.82 spid4 
  2004-03-31 14:11:08.82 spid4 Wait-for graph
  2004-03-31 14:11:08.82 spid4 
  2004-03-31 14:11:08.82 spid4 Node:1
  2004-03-31 14:11:08.82 spid4 KEY: 7:1333579789:1 (0100e420fa5a) CleanCnt:2 Mode: S Flags: 0x0
  2004-03-31 14:11:08.82 spid4 Wait List:
  2004-03-31 14:11:08.82 spid4 Owner:0xd4a59a0 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:112 ECID:0
  2004-03-31 14:11:08.82 spid4 SPID: 112 ECID: 0 Statement Type: UPDATE Line #: 52
  2004-03-31 14:11:08.82 spid4 Input Buf: Language Event: exec bts_PurgeMessages
  2004-03-31 14:11:08.82 spid4 Requested By: 
  2004-03-31 14:11:08.82 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:95 ECID:0 Ec:(0x4E5155B8) Value:0x4fae9340 Cost:(0/E60)
  2004-03-31 14:11:08.82 spid4 
  2004-03-31 14:11:08.82 spid4 Node:2
  2004-03-31 14:11:08.82 spid4 KEY: 7:1333579789:1 (0100e420fa5a) CleanCnt:2 Mode: S Flags: 0x0
  2004-03-31 14:11:08.82 spid4 Grant List 0::
  2004-03-31 14:11:08.82 spid4 Owner:0x4ef89d40 Mode: S Flg:0x0 Ref:1 Life:02000000 SPID:102 ECID:0
  2004-03-31 14:11:08.82 spid4 SPID: 102 ECID: 0 Statement Type: INSERT Line #: 100
  2004-03-31 14:11:08.82 spid4 Input Buf: Language Event: exec bts_CopyTrackedMessages
  2004-03-31 14:11:08.82 spid4 Requested By: 
  2004-03-31 14:11:08.82 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:112 ECID:0 Ec:(0x6DE055F8) Value:0xd4a59a0 Cost:(1/0)
  2004-03-31 14:11:08.82 spid4 
  2004-03-31 14:11:08.82 spid4 Node:3
  2004-03-31 14:11:08.82 spid4 KEY: 7:1093578934:1 (7d0166a07086) CleanCnt:1 Mode: X Flags: 0x0
  2004-03-31 14:11:08.82 spid4 Grant List 3::
  2004-03-31 14:11:08.82 spid4 Owner:0x4e655d20 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:95 ECID:0
  2004-03-31 14:11:08.82 spid4 SPID: 95 ECID: 0 Statement Type: SELECT Line #: 9
  2004-03-31 14:11:08.82 spid4 Input Buf: RPC Event: dbo.bts_InsertMessageDirect_BizTalkServerApplication;1
  2004-03-31 14:11:08.82 spid4 Requested By: 
  2004-03-31 14:11:08.82 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:102 ECID:0 Ec:(0x6B9415F8) Value:0xa5eb860 Cost:(1/2D6AC)
  2004-03-31 14:11:08.82 spid4 Victim Resource Owner:
  2004-03-31 14:11:08.82 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:112 ECID:0 Ec:(0x6DE055F8) Value:0xd4a59a0 Cost:(1/0)

• BizTalk Server 2004 is configured to track whole message bodies.
• BizTalk Server 2004 is under a heavy load.

Service pack information

Hotfix information

Prerequisites

Restart requirement

Hotfix replacement information

File information

   Date         Time   Size     File name
   -------------------------------------------------------
   18-Aug-2004  20:00    1,906  Droptriggers.sql
   13-Aug-2004  15:14  297,826  Msgboxlogic.sql
   12-Aug-2004  05:28    6,385  Msgbox_tracking_logic.sql
   12-Aug-2004  05:28   10,255  Msgbox_tracking_schema.sql