Delete WorkflowInstance from Database Tables
I use one DB for Persistence and Tracking. This database therefore holds all workflows started. These workflow were started for different portals used by different clients. So if a client needs to be setup from scratch, I need to find the according workflows in the database and delete them. The other workflows (for the other clients) should stay untouched.
Because of the amount of tables created (Persistence only uses 2 tables, Tracking about 20) I wanted to ask if someone knows how to do a task like this.
The table "Workflow" holds the different workflows. Now I have a reference (WorkflowTypeId) which I can use to delete the instances. Am I correct?
Thanks
[700 byte] By [
jamba8] at [2007-12-23]
it seems to be ok, but I encourage you to take a look at this article (Tracking Service data maintenance from Moustafa, MS). Does that help ?
Serge
To be honest: not really.
I want to clear the database of all workflows (no matter if they are running or not) of a specific client (Workflow Type). I will try to do as I posted (by using the WorkflowTypeId).
Any other suggestions?
-
Is there detailed information about each single table generated by the Persistence and Tracking script for the accorging services concerning use and content?
I'm confused by "specific client (Workflow Type)". Do you mean that you want to delete all workflows of a certain type (assembly qualified name for example) or do you mean that you want to delete all workflows started by a specific client?
If you want to clean up workflows for a specific type you can query the Type table in tracking and get the TypeId. Then join to the Workflow and to WorkflowInstance:
select
wi.WorkflowInstanceId, wi.WorkflowInstanceInternalIdfrom
Type tinner
join Workflow won
t.TypeId = w.WorkflowTypeIdinner
join WorkflowInstance wion
w.WorkflowTypeId = wi.WorkflowTypeIdwhere
t.TypeFullName = --<type full name> i.e. 'Microsoft.WinOE.Test.TestCases.Hosting.Providers.Tracking.OrderStateMachine.OrderWorkflow'and
t.AssemblyFullName = --<assembly full name> i.e. 'Microsoft.WinOE.Test.TestCases.Hosting.Tracking, Version=3.0.0.0, Culture=neutral, PublicKeyToken=null'This will give you all of the instance ids (guids) and internal id (bigints). Use the guids to delete records from the persistence tables - InstanceState and CompletedScopes (uidInstanceID column in both tables). Use the bigints to delete records from the tracking tables.
Thanks,
Joel West
MSFTE - SDE in WF runtime and hosting
This posting is provided "AS IS" with no warranties, and confers no rights
I think this information helps.
I already made a script which uses the "DeleteWorkflowInstance" Procedures provided with the built in sql scripts.
What you think of using this SP?
I hadn't really thought of that proc because it is part of our data partitioning/data maintainence story and not generally intended for public use. That being said it should do what you want for the tracking tables (it doesn't touch the persistence tables). You'll still need the query above to get a list of WorkflowInstanceInternalIds for the type you want to purge.
Also note that there is a redundant join in that query on the Workflow table. I added this for clarity and to demonstrate the relationships between the tables but I forgot to mention it in that post. You can join the Type table directly to the WorkflowInstance table on Type.TypeId = WorkflowInstance.WorkflowTypeId.
Thanks,
Joel West
MSFTE - SDE in WF runtime and hosting
This posting is provided "AS IS" with no warranties, and confers no rights
The schema for the Tracking database (tables, columns as well as stored
procedures) is fully documented on the WF documentations in the section
titled "Workflow Tracking Service Database". The persistence database
however is not documented since we don’t expect customers to modify it
directly. For cleaning out the persistence database, I would recommend
programmatically looping on all workflow instances and calling
"Terminate" on them, so they are unloaded from memory and deleted from
the persistence database.
I could not find the section "Workflow Tracking Service Database" in the Windows Workflow Foundation Help. Could you specify where to download this help, or how to access it, as well as the appropriate link.
Thanks
not in the online sdk, but in the downloaded (RC4) sdk documentation:
follow the path:
.Net Framework 3.0 documentation->general References->Windows Workflow Foundation General Reference->Workflow Tracking Service Database
serge
