Needless to say, I was a bit surprised to see that my BAMPrimaryImport database had grown to almost 40 GB:
Wait, what??? I went through and checked all my configurations to make sure everything was running correctly. Firstly, I checked to make sure the SQL Job that I had created to run all the BAM SSIS packages was working. BizTalk Bill had set-up an SSIS package to do this, but I went down the route of creating two SQL Jobs, the first used to dynamically build and execute the list of packages to run (any package starting with DM_) and the second to control and monitor the execution of the first. Both jobs looked like they were running successfully (These are run on a nightly basis).
My second check was to look at the BAMPrimaryImport database tables. I first did a visual inspection of the tables, in which I noticed an unusual amount of partition tables for the first message being tracked using BAM activities and views:
Secondly, I ran a quick SQL Command to return the number of tables in the BAMPrimaryImport database:
I had over 7000 tables in the BAMPrimaryImport database!!! Granted, we do a lot of BAM tracking on all of our different messages, but that number sounded excessive. So I wanted to confirm what Richard Seroter had writtien in his blog and looked at the Metadata_Activities table to see how long data should be kept before archiving. As I suspected, it was configured to only keep a months worth of tracking data:
So from the above i could determine:
- SQL Jobs were running - bueno
- SSIS packages creating table partitions - bueno
- Partition tables being archived to the BAMArchive database - no bueno
So why were the partition tables not being moved to the BAMArchive database? In looking at the properties on some of the tables, they had been created way back in October and November of 2014. In between looking at the aforementioned blog posts, I noticed something different in my Integration Services environment. I had what appeared to be a lot of SSIS packages not only starting with "DM_", but "AN_" as well.
I did a quick google search with the AN SSIS packages and found a great article by the Microsoft India team. In the last paragraph of the article I found my problem. It appears, that if in your BAM tracking you take advantage of creating OLAP cubes, you need to set-up the SSIS Packages that begin with "AN_" to run daily. If you fail to do this step, the partition tables will fail to be moved to the BAMArchive database.
As a test, I went ahead and ran the "AN_" SSIS package for the first tracked message. I then went and executed the "DN_" SSIS package for that same tracked message. Sure enough, the appropriate partition tables were moved to the BAMArchive table:
In order to play it safe and get the appropriate tables archived, I manually ran each SSIS package that had the "AN_" prefix (all 143 of them). What's worse about this whole ordeal is that the current environment in which I work in doesn't even use these OLAP cubes (you can read why here). I have to admit this was a sloppy mistake on my part, and shows how little I really understood the archiving process when using BAM. On a positive note, maybe I can convince the BizTalk360 to automate this process in an upcoming release?