Cleaning up BAM Activity Instances
One of the main challenges of Business Activity Monitoring(BAM) solutions is the control the lifecycle of the activities. This is especially critical in scenarios that involve activity continuations between different applications. Far too many times activities don't get completed affecting important BAM components such as aggregations, KPIs, etc. More importantly, keeping incomplete activities stored in the "ActivityName"_Active tables in the BAMPrimaryImport database can tremendously affect the performance of BizTalk BAM solutions. As part of the BizTalk server SDK we can find a SQL Server stored procedure that moves the incomplete activities from the Active tables to a new set of tables, the essence of this stored procedure is to add the incomplete state to the BAM Activity lifecycle creating a new table per BAM activity as well as its continuations and relationships. This stored procedure recycles individual activities and keeps the incomplete tables in the BAMPrimaryImport database. Although this approach works great, for a lot of scenarios makes sense to move the incomplete activities directly to the BAMArchive database keeping the BAMPrimaryImport database for storing live data. We can accomplish that with a few slight modifications to the same stored procedure highlighted in the following code. This stored procedure is intended to run in the BAMArchive database but it will poll the data from the BAMPrimaryImport database (notice the database prefix in some of the queries)
|
Create procedure CleanupUncompleteActivity @ActivityName nvarchar (128), @ActivityId nvarchar(128)=NULL, @DateThreshold datetime=NULL, @NewTableExtension nvarchar(30)=NULL as --stored procedure body DECLARE @BAMDBPrefix nvarchar(500 ) DECLARE @QueryString nvarchar(4000 ) DECLARE @ActiveTableName sysname DECLARE @ActiveRelationshipsTableName sysname DECLARE @ContinuationsTableName sysname DECLARE @DanglingActiveTableName sysname DECLARE @DanglingActiveRelationshipsTableName sysname DECLARE @DanglingContinuationsTableName sysname SET @BAMDBPrefix= 'BAMPrimaryImport.dbo.' SET @ActiveTableName = 'bam_' + @ActivityName + '_Active' SET @ActiveRelationshipsTableName = 'bam_' + @ActivityName + '_ActiveRelationships' SET @ContinuationsTableName = 'bam_' + @ActivityName + '_Continuations' SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN DECLARE @LockActivity nvarchar(128 ) SELECT @LockActivity = ActivityName FROM BAMPrimaryImport.dbo.bam_Metadata_Activities WITH (XLOCK) WHERE ActivityName = @ActivityName EXEC sp_tables @table_name = #DanglingActivities IF @@ROWCOUNT > 0 DROP TABLE #DanglingActivities CREATE TABLE #DanglingActivities( ActivityID nvarchar(128) PRIMARY KEY) SET @QueryString = N'INSERT INTO #DanglingActivities (ActivityID) SELECT ActivityID FROM ' + @BAMDBPrefix + 'bam_' + @ActivityName + '_Active' IF (@DateThreshold is not NULL) OR (@ActivityId is not NULL) BEGIN SET @QueryString = @QueryString + ' WHERE' END IF (@DateThreshold is not NULL) BEGIN SET @QueryString = @QueryString + ' LastModified < N''' + CONVERT (nvarchar(50), @DateThreshold , 109) + '''' IF (@ActivityId is not NULL) BEGIN SET @QueryString = @QueryString + ' AND' END END IF (@ActivityId is not NULL) BEGIN SET @QueryString = @QueryString + ' ActivityID = N''' + @ActivityId + '''' END EXEC sp_executesql @QueryString SELECT * FROM #DanglingActivities SET @QueryString = N'' -- If the user gave a table extension, the dangling instances will be inserted -- into that table. IF (isnull(@NewTableExtension , '') <> '') BEGIN SET @DanglingActiveTableName = @ActiveTableName + '_' + @NewTableExtension SET @DanglingActiveRelationshipsTableName = @ActiveRelationshipsTableName + '_' + @NewTableExtension SET @DanglingContinuationsTableName = @ContinuationsTableName + '_' + @NewTableExtension -- If the table for the dangling instances exist then insert into it -- If the table does not exist, then create the dangling instances table -- and then insert into it. SELECT INTO will do that. -- Use [BAMArchive] EXEC sp_tables @table_name = @DanglingActiveTableName --USE [BAMPrimaryImport] IF @@ROWCOUNT > 0 BEGIN SET @QueryString = N'INSERT INTO ' + '[' + @DanglingActiveTableName + '] SELECT active.* FROM ' + @BAMDBPrefix + @ActiveTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID' EXEC sp_executesql @QueryString END ELSE BEGIN SET @QueryString = N'SELECT active.* INTO [' + @DanglingActiveTableName + '] FROM ' + @BAMDBPrefix + @ActiveTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID' select @QueryString EXEC sp_executesql @QueryString END -- Now do what you did for the Active Instances table for the -- ActiveRelationships table EXEC sp_tables @table_name = @DanglingActiveRelationshipsTableName IF @@ROWCOUNT > 0 BEGIN SET @QueryString = N'INSERT INTO ' + '[' + @DanglingActiveRelationshipsTableName + '] SELECT active.* FROM ' + @BAMDBPrefix + @ActiveRelationshipsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID' EXEC sp_executesql @QueryString END ELSE BEGIN SET @QueryString = N'SELECT active.* INTO [' + @DanglingActiveRelationshipsTableName + '] FROM ' + @BAMDBPrefix+ @ActiveRelationshipsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID' EXEC sp_executesql @QueryString END -- And finally for the continuations table EXEC sp_tables @table_name = @DanglingContinuationsTableName IF @@ROWCOUNT > 0 BEGIN SET @QueryString = N'INSERT INTO ' + '[' + @DanglingContinuationsTableName + '] SELECT active.* FROM ' + @BAMDBPrefix + @ContinuationsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ParentActivityID = dangling.ActivityID' EXEC sp_executesql @QueryString END ELSE BEGIN SET @QueryString = N'SELECT active.* INTO [' + @DanglingContinuationsTableName + '] FROM ' + @BAMDBPrefix + @ContinuationsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ParentActivityID = dangling.ActivityID' EXEC sp_executesql @QueryString END END -- Remove the dangling instances from the Active Instances Table SET @QueryString = 'DELETE FROM ' + @BAMDBPrefix + @ActiveTableName + ' FROM ' + @BAMDBPrefix + @ActiveTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID ' EXEC sp_executesql @QueryString SET @QueryString = 'DELETE FROM ' + @BAMDBPrefix + @ActiveRelationshipsTableName + ' FROM ' + @BAMDBPrefix + @ActiveRelationshipsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID ' EXEC sp_executesql @QueryString SET @QueryString = 'DELETE FROM ' + @BAMDBPrefix + @ContinuationsTableName + ' FROM ' + @BAMDBPrefix + @ContinuationsTableName + ' as active INNER JOIN #DanglingActivities dangling ON active.ParentActivityID = dangling.ActivityID ' EXEC sp_executesql @QueryString DROP TABLE #DanglingActivities COMMIT TRAN GO |
As we mentioned previously, the stored procedure recycle individual activities. In case you need to cleanup multiple activities at the same time you can loop though the list of activities stored in the bam_Metadata_Activities table in the BAMPrimaryImport database. The following code illustrates that technique.
|
Create procedure CleanupUncompleteActivities @DateThreshold datetime =NULL, @NewTableExtension nvarchar(30)=NULL as declare @activityname nvarchar(250) declare actcursor cursor for select ActivityName from BAMPrimaryImport. dbo.bam_Metadata_Activities open actcursor FETCH NEXT FROM actcursor INTO @activityname WHILE @@FETCH_STATUS = 0 begin execute CleanupUncompleteActivity @ActivityName= @activityname , @NewTableExtension= 'Uncompleted', @DateThreshold= @DateThreshold FETCH NEXT FROM actcursor INTO @activityname end close actcursor deallocate actcursor
|
You can download both stored procedure here but in any case we don't advice using these in production environments.