BAM Portal Activity Search: "This action cannot be performed because one or more database(s) appears corrupted"

When using the BAM Portal Activity Search feature, you may be directed to an error page with the above error message.

This error message means that there are instances with duplicate ID in the activity you were searching. If you look at the "Application" event log on the computer which runs the BAM Web Services, you will find one entry from "BAM Web Service" comparable to the following:

System.Web.Services.Protocols.SoapException: There are instances with duplicate ID 'PO_COMPLETED_100' in activity 'PurchaseOrder' . The duplicates must be removed from the database to fix this problem.

BAM will not operate properly if there are duplicate activity instance IDs. Internally, BAM uses these IDs to perform correlations and expects them to be unique. The database is considered corrupted when this situation occurs and this condition should be fixed as soon as possible.

Why does that happen?

You installed one of the BAM sample or tutorial and caused BizTalk to process the same message more than once. Sample messages have hardcoded IDs, so if you process the same message twice, you'll end up with two activity instances with the same ID. 

This can also happen if BAM was asked to insert data with duplicate activity instance IDs through the BAM APIs such as DirectEventStream or BufferedEventStream.

You may also extract activity IDs from incoming messages using a tracking profile and two messages came with the same ID.

For BAM, it is critical you ensure all activity instasnce IDs are unique.

So why does BAM allow me to insert duplicate IDs?

If BAM cannot operate with duplicate activity instance IDs, why doesn't BAM refuse to insert them in the first place? Well, this is for performance reasons. Internally, BAM splits the data into partitions to make insertions faster. Checking for the uniqueness of a key would require to lock multiple tables in a transaction and scan for this ID into multiple partitions / tables. This would dramatically slow BAM down.