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 (1)

  1. To my great surprise, after doing a search for a script that would delete the events logged by the almighty