Deleting Orphan Attachments
Microsoft BizTalk Accelerator for RosettaNet (BTARN) stores attachments for received messages. In certain circumstances, BTARN saves the attachment, but deletes the associated message from the MessagesToLOB table, resulting in an orphan attachment. This can occur when you submit a message that has an attachment and has a manifest that is not valid, for example, a manifest in which NumberOfAttachments = 0. Periodically, you may want to delete orphan attachments to maintain system performance.
How to Delete Orphan Attachments
BTARN stores attachments in the Attachments table of the BTARNDATA database. BTARN stores the associated messages in the MessagesToLOB table. An orphan attachment results when the attachment has an outMessageID property that does not correspond to the MessageID property of a message in the MessagesToLOB table.
Periodically, you may want to delete attachments from the table by using a stored procedure that deletes only those attachments that do not have a corresponding message in the MessagesToLOB table. A sample SQL statement for the stored procedure is:
delete from attachments where outMessageID not in (select messageid from messagestolob)
Additionally, it is recommended that you delete attachments that are older than a certain period, and do not require any more investigation. The Attachments table contains a TimeCreated property that you can use to delete old attachments. This process is similar to the process used to delete old digests. For a sample SQL statement for a stored procedure that deletes old digests, see Deleting Digests.
It is also recommended that you index the Attachments and MessagestoLOB tables on the respective MessageID columns.