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>