Workflow Tracking DB Cleanup Script

If you are using the Workflow Tracking database, you may find that it grows at a rapid pace depending on the configured tracking level.

The attached script can be run against the tracking database to clean out records on a regular basis. The steps below describe the parameter that needs to be entered and what is needed to schedule it as an SQL job.

  1. Use the script sproc_WorkFlowTracking_Delete_Tables_DateParameter.sql. Download or copy it below.

    The script only requires a date parameter to be populated. In the scenario below, anything older than 10 days would be deleted.


    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES r WHERE r.routine_name='sproc_WorkFlowTracking_Delete_Tables_DateParameter' and r.routine_schema='dbo')
	DROP PROCEDURE dbo.sproc_WorkFlowTracking_Delete_Tables_DateParameter
GO

/****** Object:  StoredProcedure [dbo].[sproc_WorkFlowTracking_Delete_Tables_DateParameter]    Script Date: 10/9/2015 10:42:47 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE    [dbo].[sproc_WorkFlowTracking_Delete_Tables_DateParameter] 
 -- Add the parameters for the stored procedure here  
 @NumberOfDays int  
AS  
BEGIN  

/*
Exec [dbo].[sproc_WorkFlowTracking_Delete_Tables_DateParameter]  10
*/
	-- SET NOCOUNT ON added to prevent extra result sets from  
	-- interfering with SELECT statements.  
	SET NOCOUNT ON;  
  
	DECLARE @HowManyRecordsTobeDeleted INT;
	DECLARE @InitialSet INT;
	Set @InitialSet = 500;
	SET @HowManyRecordsTobeDeleted = @InitialSet;

	WHILE @HowManyRecordsTobeDeleted > 10
	BEGIN
		
		BEGIN TRY  
			BEGIN TRAN
	
			   delete  top (@HowManyRecordsTobeDeleted) [workflowtracking].[System.Workflow.Tracking].[WorkflowInstanceEventsTable]  from [workflowtracking].[System.Workflow.Tracking].[WorkflowInstanceEventsTable]      
			   where  (DATEDIFF(day,[workflowtracking].[System.Workflow.Tracking].[WorkflowInstanceEventsTable].TimeCreated,Getdate())>=@NumberOfDays)   
			SET @HowManyRecordsTobeDeleted = @@ROWCOUNT
			print 'deleted WorkflowInstanceEventsTable'
			COMMIT TRAN
		 END TRY
		 BEGIN CATCH
			ROLLBACK TRAN
			set @HowManyRecordsTobeDeleted = 0
			print 'ERROR in deleting WorkflowInstanceEventsTable'
		END CATCH

	END
	set @HowManyRecordsTobeDeleted = @InitialSet
	WHILE @HowManyRecordsTobeDeleted > 0
	BEGIN
			
		BEGIN TRY  
			BEGIN TRAN
	
			   delete top (@HowManyRecordsTobeDeleted) [workflowtracking].[System.Workflow.Tracking].[ActivityInstanceEventsTable]  from [workflowtracking].[System.Workflow.Tracking].[ActivityInstanceEventsTable] st
			   where     (DATEDIFF(day,st.TimeCreated,Getdate())>=@NumberOfDays)
			SET @HowManyRecordsTobeDeleted = @@ROWCOUNT
			print 'deleted ActivityInstanceEventsTable'
			COMMIT TRAN
		  END TRY
		 BEGIN CATCH
			ROLLBACK TRAN
			set @HowManyRecordsTobeDeleted = 0
			print 'ERROR in deleting ActivityInstanceEventsTable'
		END CATCH

	END

	set @HowManyRecordsTobeDeleted = @InitialSet
	WHILE @HowManyRecordsTobeDeleted > 0
	BEGIN
		BEGIN TRY  
			BEGIN TRAN
	
				delete  top (@HowManyRecordsTobeDeleted) [workflowtracking].[System.Workflow.Tracking].[ExtendedActivityEventsTable] from [workflowtracking].[System.Workflow.Tracking].[ExtendedActivityEventsTable]   stc      
			   where (DATEDIFF(day,stc.TimeCreated,Getdate())>=@NumberOfDays)
			SET @HowManyRecordsTobeDeleted = @@ROWCOUNT
			print 'deleted ExtendedActivityEventsTable'
			COMMIT TRAN
			END TRY
		  BEGIN CATCH
			ROLLBACK TRAN
			set @HowManyRecordsTobeDeleted = 0
			print 'ERROR in deleting ExtendedActivityEventsTable'
		END CATCH

	END

	set @HowManyRecordsTobeDeleted = @InitialSet
	WHILE @HowManyRecordsTobeDeleted > 0
	BEGIN
		BEGIN TRY  
			BEGIN TRAN
	
				delete  top (@HowManyRecordsTobeDeleted) [workflowtracking].[System.Workflow.Tracking].[BookmarkResumptionEventsTable] from [workflowtracking].[System.Workflow.Tracking].[BookmarkResumptionEventsTable]   stc      
			   where (DATEDIFF(day,stc.TimeCreated,Getdate())>=@NumberOfDays)
			SET @HowManyRecordsTobeDeleted = @@ROWCOUNT
			print 'deleted BookmarkResumptionEventsTable'
			COMMIT TRAN
			END TRY
		  BEGIN CATCH
			ROLLBACK TRAN
			set @HowManyRecordsTobeDeleted = 0
			print 'ERROR in deleting BookmarkResumptionEventsTable'
		END CATCH

	END
	
	set @HowManyRecordsTobeDeleted = @InitialSet
	WHILE @HowManyRecordsTobeDeleted > 0
	BEGIN
		BEGIN TRY  
			BEGIN TRAN
	
				delete  top (@HowManyRecordsTobeDeleted) [workflowtracking].[System.Workflow.Tracking].[CustomTrackingEventsTable] from [workflowtracking].[System.Workflow.Tracking].[CustomTrackingEventsTable]   stc      
			   where (DATEDIFF(day,stc.TimeCreated,Getdate())>=@NumberOfDays)
			SET @HowManyRecordsTobeDeleted = @@ROWCOUNT
			print 'deleted CustomTrackingEventsTable'
			COMMIT TRAN
			END TRY
		  BEGIN CATCH
			ROLLBACK TRAN
			set @HowManyRecordsTobeDeleted = 0
			print 'ERROR in deleting CustomTrackingEventsTable'
		END CATCH

	END

END  

GO

  2. The script can also be scheduled as an SQL job to run based on a schedule.

    SQL Job Agent Schedule

  3. You can control the amount of data being tracked by using trackingProfiles (defined within the Service Module Host config file).

    Notes:

    • If tracking is configured to track variables, this database can grow extremely fast.

    • If you do not want tracking enabled, you can remove the tracking profile from the config file.

    • If you simply want to track the start and stop of a workflow, we recommend the following setting:

      <system.serviceModel>
    <tracking>
      <profiles>
        <trackingProfile name="DefaultTrackingProfile">
          <workflow activityDefinitionId="*">
            <workflowInstanceQueries>
              <workflowInstanceQuery>
                <states>
                  <state name="Started" />
                  <state name="Completed" />
                </states>
              </workflowInstanceQuery>
            </workflowInstanceQueries>
          </workflow>
        </trackingProfile>
      </profiles>
    </tracking>
  </system.serviceModel>