SYSK 359: How to Purge Workflow Tracking Data Inserted By SqlTrackingService

To my great surprise, after doing a search for a script that would delete the events logged by the almighty SqlTrackingService service, I didn’t find one… So, I quickly created it (see below), and it seems to do the job just fine given the current table schema implementation used by the service:

IF OBJECT_ID('TrackingPartitionSetName') IS NOT NULL

      DELETE dbo.TrackingPartitionSetName

GO

IF OBJECT_ID('TrackingPartitionInterval') IS NOT NULL

      DELETE dbo.TrackingPartitionInterval

GO

IF OBJECT_ID('TrackingProfileInstance') IS NOT NULL

      DELETE dbo.TrackingProfileInstance

GO

IF OBJECT_ID('TrackingProfile') IS NOT NULL

      DELETE dbo.TrackingProfile

GO

IF OBJECT_ID('[dbo].[AddedActivity]') IS NOT NULL

      DELETE [dbo].[AddedActivity]

GO

IF OBJECT_ID('[dbo].[RemovedActivity]') IS NOT NULL

      DELETE [dbo].[RemovedActivity]

GO

IF OBJECT_ID('TrackingDataItemAnnotation') IS NOT NULL

      DELETE dbo.TrackingDataItemAnnotation

GO

IF OBJECT_ID('EventAnnotation') IS NOT NULL

      DELETE dbo.EventAnnotation

GO

IF OBJECT_ID('TrackingDataItem') IS NOT NULL

      DELETE dbo.TrackingDataItem

GO

IF OBJECT_ID('ActivityExecutionStatusEvent') IS NOT NULL

      DELETE dbo.ActivityExecutionStatusEvent

GO

IF OBJECT_ID('UserEvent') IS NOT NULL

      DELETE dbo.UserEvent

GO

IF OBJECT_ID('ActivityInstance') IS NOT NULL

      DELETE dbo.ActivityInstance

GO

IF OBJECT_ID('WorkflowInstanceEvent') IS NOT NULL

      DELETE [dbo].[WorkflowInstanceEvent]

GO

IF OBJECT_ID('WorkflowInstance') IS NOT NULL

      DELETE dbo.WorkflowInstance

GO

IF OBJECT_ID('[dbo].[Activity]') IS NOT NULL

      DELETE [dbo].[Activity]

GO

IF OBJECT_ID('[dbo].[Workflow]') IS NOT NULL

      DELETE [dbo].[Workflow]

GO

IF OBJECT_ID('Type') IS NOT NULL

      DELETE dbo.Type

GO

/* Uncomment the section below if you want to re-set the lookups

IF OBJECT_ID('ActivityExecutionStatus') IS NOT NULL

      DELETE dbo.ActivityExecutionStatus

GO

-- Reinsert lookups

INSERT dbo.ActivityExecutionStatus VALUES ( 0, N'Initialized' )

INSERT dbo.ActivityExecutionStatus VALUES ( 1, N'Executing' )

INSERT dbo.ActivityExecutionStatus VALUES ( 2, N'Canceling' )

INSERT dbo.ActivityExecutionStatus VALUES ( 3, N'Closed' )

INSERT dbo.ActivityExecutionStatus VALUES ( 4, N'Compensating' )

INSERT dbo.ActivityExecutionStatus VALUES ( 5, N'Faulting' )

GO

IF OBJECT_ID('TrackingWorkflowEvent') IS NOT NULL

      DELETE [dbo].[TrackingWorkflowEvent]

GO

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 0, N'Created' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 1, N'Completed' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 2, N'Idle' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 3, N'Suspended' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 4, N'Resumed' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 5, N'Persisted' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 6, N'Unloaded' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 7, N'Loaded' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 8, N'Exception' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 9, N'Terminated' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 10, N'Aborted' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 11, N'Changed' )

INSERT [dbo].[TrackingWorkflowEvent] VALUES ( 12, N'Started' )

       

GO

IF OBJECT_ID('DefaultTrackingProfile') IS NOT NULL

      DELETE dbo.DefaultTrackingProfile

GO

INSERT [dbo].[DefaultTrackingProfile] ( [Version], [TrackingProfileXml] )

VALUES (

'1.0.0',

N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>

<TrackingProfile xmlns="http://schemas.microsoft.com/winfx/2006/workflow/trackingprofile" version="1.0.0">

    <TrackPoints>

        <ActivityTrackPoint>

            <MatchingLocations>

       <ActivityTrackingLocation>

                    <Activity>

                        <Type>System.Workflow.ComponentModel.Activity, System.Workflow.ComponentModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</Type>

             <MatchDerivedTypes>true</MatchDerivedTypes>

                    </Activity>

                    <ExecutionStatusEvents>

                        <ExecutionStatus>Initialized</ExecutionStatus>

                        <ExecutionStatus>Executing</ExecutionStatus>

                        <ExecutionStatus>Compensating</ExecutionStatus>

                        <ExecutionStatus>Canceling</ExecutionStatus>

                        <ExecutionStatus>Closed</ExecutionStatus>

                        <ExecutionStatus>Faulting</ExecutionStatus>

                    </ExecutionStatusEvents>

                </ActivityTrackingLocation>

            </MatchingLocations>

        </ActivityTrackPoint>

            <WorkflowTrackPoint>

                  <MatchingLocation>

                        <WorkflowTrackingLocation>

                              <TrackingWorkflowEvents>

                                    <TrackingWorkflowEvent>Created</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Completed</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Idle</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Suspended</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Resumed</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Persisted</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Unloaded</TrackingWorkflowEvent>

                              <TrackingWorkflowEvent>Loaded</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Exception</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Terminated</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Aborted</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Changed</TrackingWorkflowEvent>

                                    <TrackingWorkflowEvent>Started</TrackingWorkflowEvent>

                              </TrackingWorkflowEvents>

                        </WorkflowTrackingLocation>

                  </MatchingLocation>

            </WorkflowTrackPoint>

        <UserTrackPoint>

            <MatchingLocations>

                <UserTrackingLocation>

                    <Activity>

                        <Type>System.Workflow.ComponentModel.Activity, System.Workflow.ComponentModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</Type>

                        <MatchDerivedTypes>true</MatchDerivedTypes>

                    </Activity>

                    <Argument>

                        <Type>System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</Type>

                        <MatchDerivedTypes>true</MatchDerivedTypes>

                    </Argument>

                </UserTrackingLocation>

            </MatchingLocations>

        </UserTrackPoint>

    </TrackPoints>

</TrackingProfile>' )

GO

*/

Comments

• Anonymous
  August 05, 2007
  To my great surprise, after doing a search for a script that would delete the events logged by the almighty