BAM: “..One of more database(s) appears corrupted..”

I got this error.

Checking the event log I saw a BAM Web Service Error like

System.Web.Services.Protocols.SoapException: There are instances with duplicate ID ‘\\MyServer\customer-b2c-dw_wsl_delta201604131734004.xml’ in activity ‘GenericCountOfTrackedMessages’. The duplicates must be removed from the database to fix this problem.

Running the following query showed the duplicates in two of the partitions.

SELECT * FROM [BAMPrimaryImport].[dbo].[bam_GenericCountOfTrackedMessages_20ED6B1B_734C_4980_B213_792B16DF734B] where ActivityID IN (SELECT ActivityID FROM dbo.bam_GenericCountOfTrackedMessages_00C024D3_BCC3_4E02_A428_3358E482BF4F)

I decided to delete these duplicates from one of the partitions with

DELETE

–SELECT *

FROM [BAMPrimaryImport].[dbo].[bam_GenericCountOfTrackedMessages_20ED6B1B_734C_4980_B213_792B16DF734B] where ActivityID IN (SELECT ActivityID FROM dbo.bam_GenericCountOfTrackedMessages_00C024D3_BCC3_4E02_A428_3358E482BF4F)

This worked and the query of this BAM activity now works without the above error.