Purging archived BAM data

  • Article

BAM is one of the most underutilised features of BizTalk, but when it is used it can provide very valuable insights into the messages and the processes that BizTalk manages.  Left unchecked, however, BAM can quickly consume disk space as it accumulates data.  Although the build-up of data will not necessarily impact on BizTalk's performance, it can cause disk space issues if not maintained.  There are two ways to deal with this: purge BAM data without archiving it, and to move data into an archive store.  While the first is handled by SQL, the second method lacks a process to ensure that archived data is purged after an archive window period.

Purge without Archiving

One option for managing the growth in data is to store the BAM data for a certain period of time, after which it should be purged.  This is achieved by defining a time period for which an activity's BAM data should be kept, and by instructing BAM to not backup the data after the time period expires.

  • To define the time period for keeping the data, you would set the TimeLength and TimeUnit attributes on a BAM activity, using the set-activitywindow command.  The command bm.exe set-activitywindow -Activity:PurchaseOrder -TimeLength:1 -TimeUnit:Month would ensure that BAM data for the PurchaseOrder Activity is kept for one month before being archived or purged.
  • By doing the above and the using the set-archive command to instruct BAM not to archive the data (bm.exe set-archive -Activity:PurchaseOrder -ShouldArchive:False) the BAM infrastructure will ensure that any data older than the provided time window will be purged.

(Reference of BAM Activity management commands: https://msdn.microsoft.com/en-us/library/bb246031.aspx)

Archiving without Purge

The second option is to maintain the time window, as with the first option, and to enable archiving (bm.exe set-archive -Activity:PurchaseOrder -ShouldArchive:True).  This will instruct the BAM infrastructure to move any data older than the provided time window into the BAMArchive database before removing the data from the BAMPrimaryImport database tables.  Within the BAMArchive database, one table per day per Activity will be created to store the archived data.

This is a great way to ensure that the size of the BAMPrimaryImport tables are kept under control, but there are two issues that need to be kept in mind with this approach:

  1. Once the data is moved to the BAMArchive database, the BAM Views will no longer reference this data, so queries on archived data will need to be constructed manually.
  2. There is no automatic process for removing data from the BAMArchive database, so over time the number of tables in the BAMArchive database will increase and the disk space utilised by this database will continue to grow.

Having this archived data available when you want to do some historical analysis on the data is very useful, but there is a tipping point where the amount of space the data utilises outweighs the benefits of keeping the data on hand.  It is at this point that an automated mechanism for cleaning up the BAMArchive database becomes required. 

BAM Archive cleanup Script

To this end, the below script is a script that I have utilised to automate this process.  The script queries the sys.objects container for any user tables that start with the text "bam_", and which have been modified more than 45 days ago.  It then uses a cursor to iterate over the result, and drops each table in turn.

NOTE: As this script is not restricted to a particular database, it will drop any tables in any database, where the table meets the defined criteria. Please review the script carefully before implementing.  

For this purpose, the execution statement to actually drop the table has been commented out. Once you are happy that the logic is safe, you can uncomment this line, implement the stored procedure and then schedule its execution from a SQL Agent job.

 CREATE PROCEDURE [dbo].[usp_CleanUpBAMArchiveTables]
AS
BEGIN
 DECLARE @tname VARCHAR(100)
 DECLARE @sql VARCHAR(max)
  
 DECLARE db_cursor CURSOR FOR 
 SELECT name AS tname
 FROM sys.objects
 WHERE 
 modify_date < GETDATE() - 45
 AND name LIKE 'bam_%'
 AND type='U'
  
 OPEN db_cursor 
 FETCH NEXT FROM db_cursor INTO @tname 
  
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
 SET @sql = 'DROP TABLE [' + @tname + ']'
 --EXEC (@sql)
 PRINT @sql
  
 FETCH NEXT FROM db_cursor INTO @tname 
 END 
  
 CLOSE db_cursor 
 DEALLOCATE db_cursor
END