BAM Activities not getting completed

Recently, I was working on a high volume BAM environment, where nearly 1-2 million activity instances were getting inserted into a particular BAM activity. The activity fields in this activity were mapped to a couple of orchestrations, with a continuation linking the two orchestrations into a single activity.

Strangely, some thousands of these Activity Instances were not getting completed each day. One possible reason for Activity Instances not getting completed is that the continuation fragment that is expected for the current Activity Instance (row) has not arrived yet. In other words, in our case this could have happened if the second orchestration instance that was part of our BAM Activity had not started/completed yet.

To check if BAM activity instances are pending/not yet complete due to pending continuation fragments, you can query the bam_<ActivityName>_Continuations table with the following –
SELECT * FROM [BAMPrimaryImport].[dbo].[bam_<ActivityName>_Continuations] where ParentActivityID = ‘<ActivityID value>’
(need to replace <ActivityID value> above with the ActivityID of the activity row being checked for presence of continuations from bam_<ActivityName>_Active table) 

If the above returns no rows, you can be sure that no continuation fragments are expected for the Activity Instance (row) in question. The next thing to be checked is the isComplete column in the bam_<ActivityName>_Active table. If this column is marked as 1 and the above query yields no results, then this row in the bam_<ActivityName>_Active table is a candidate to be moved to the bam_<ActivityName>_Completed table.

Back to the issue we were facing – we noticed that for the activity instances that were not moved to the bam_<ActivityName>_ Completed table, the isComplete column was 0 – therefore to the BAM runtime, these activity instances were still incomplete and hence they were staying back indefinitely in the Active table.

So, we turned on tracking for these couple of orchestrations and tried to make sense of what was going on. We found that for all of these rows that were staying active indefinitely, the second orchestration that was part of the Activity was not completing successfully – they were getting Terminated. The orchestration flow for these particular orchestrations were ending in a terminate shape. Upon digging into this further, we found that for any orchestration that ends in a Terminate shape, the end shape event is not propagated to the BAM runtime. This in turn, doesn’t fire the EndActivity event that is necessary for an Activity row to be marked as complete. This EndActivity event is responsible for marking the isComplete column as 1.

The solution we went ahead to mitigate the issue was to manually mark these problematic Activity instances as complete through a script that was scheduled in SQL Agent. In the script, you need to first check if continuation fragments are present for a particular Activity row and if not, you can manually mark that activity row as complete.

Ref - https://msdn.microsoft.com/en-us/library/cc296683(v=bts.10).aspx

Another solution may have been to redesign the second orchestration so that so many of them did not end up as terminated – but, there may be a perfectly valid business scenario for you to terminate orchestrations.

Our only option (in that case) to prevent the bam_<ActivityName>_Active table from bloating up over time is to periodically check and mark as complete the activity instances that have no continuations pending.The following link has some further details on what can cause issues when mapping Orchestration shapes to a BAM activity – https://msdn.microsoft.com/en-us/library/ee270796(v=bts.10).aspx

Written By
Arindam Paul Roy

Reviewed By
Jainath V R 

Microsoft India GTSC