BAM Tracking data getting accumulated in the BizTalkMsgBox Database

I recently got into a situation where I noticed that the size of the tracking tables in BizTalkMsgBoxDb database kept increasing. I noticed that the tables which had larger size were supposed to hold BAM tracking data and end with ‘0_x’ (dbo. TrackingData_0_1, etc).

This meant that TDDS is not able to move data out of MsgBox database for some reasons. I checked whether I see any errors in the event log and I found below error in the event log.
Execute batch error. Exception information: TDDS failed to batch execution of streams. SQLServer: ServerName, Database: BAMPrimaryImport.Parameter is not valid.

I also saw below error message logged in the “TDDS_FailedTrackingData” table under BAMPrimaryImport database.
TDDS failed to execute event. Could not find stored procedure “dbo.bam_ActivityName_PrimaryImport'.

This stored procedure gets created when you deploy a BAM definition files for a BAM activity. There are a lot of artifacts (SPs,Tables) gets created along with this corresponding to the BAM definition we deploy. I went to the BAMPrimaryImport database and noticed that all
the artifacts (Tables + SPs) related to this activity were not in place. This means that the BAM definition file for the activity was removed by me using the “BM remove-all -DefinitionFile:EAI_BAM_definition.xml”” command. But I did not remove the corresponding tracking profile from the tracking Profile Editor.

So although I did not have any BAM definition file deployed for an activity, the tracking profile was still tracking the data. Now as the artifacts related to BAM activity were missing from database, TDDS was not able to move data out of BizTalkMsgBoxDb.
In order to get around this, I had to deploy the BAM definition file for the particular activity again. Once I did that, the missing artifacts got created and TDDS worked just fine. Data started moving out of BizTalkMsgBoxDb without any issues.
I got into this situation as I did not follow the proper steps to remove the BAM activity from my environment.
Below link talks about the steps you need to perform in order to remove BAM infrastructure for a particular activity from your environment: https://msdn.microsoft.com/en-us/library/cc339289(v=bts.20).aspx

You need to remove tracking profile before you remove BAM definition in order to make sure everything works well.

I hope this helps.

Written By
Yakshit Gohel

Reviewed By
Chirag Pavecha

Microsoft GTSC India.