FIX: The "MessageBox_Parts_Cleanup_BizTalkMsgBoxDb" SQL Server agent job runs very slowly in BizTalk Server

On a computer that is running Microsoft BizTalk Server 2006 R2, in BizTalk Server 2009 or in BizTalk Server 2010, the MessageBox_Parts_Cleanup_BizTalkMsgBoxDbSQL Server agent job runs very slowly. When this issue occurs, the job purges limited records of message parts per second from the Parts table in the BizTalkMsgBoxDb database. For example, the job only processes about 100 records per second. However, if you run the job manually in the query window, the job processes about 5000 records per second.

The issue occurs because an inefficient query plan is used in the stored procedure for the MessageBox_Parts_Cleanup_BizTalkMsgBoxDb SQL Server Agent job. This causes the job to run very slowly.

Cumulative update information

BizTalk Server 2010

For more information about how to obtain the cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

Cumulative update informationThe hotfix that resolves this issue is included in cumulative update package 2 for BizTalk Server 2009.

For more informationabout how to obtain the cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
The hotfix that resolves this problem is included in cumulative update package 2 for BizTalk Server 2006 R2 Service Pack 1.

For more information about how to obtain the cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

2211420 Cumulative update package 2 for BizTalk Server 2006 R2 Service Pack 1

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

The MessageBox_Parts_Cleanup_BizTalkMsgBoxDb SQL Server agent job removes all message parts that are no longer referenced by a message in the BizTalkMsgBoxDb database tables. All messages are composed of one or more message parts that contain the message data.

For more information about the description of the SQL Server Agent jobs in BizTalk Server, click the following article number to view the article in the Microsoft Knowledge Base:

For more information about the description of the SQL Server Agent jobs in BizTalk Server, click the following article number to view the article in the Microsoft Knowledge Base:

919776 Description of the SQL Server Agent jobs in BizTalk Server

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:For more information about BizTalk Server hotfixes, click the following article number to view the article in the Microsoft Knowledge Base:

For more information about the list of Microsoft BizTalk Server hot fixes that are included in BizTalk Server 2006 R2 Service Pack 1, click the following article number to view the article in the Microsoft Knowledge Base: