Bulk Deleting Waiting Workflow Instances from Microsoft Dynamics CRM

One of the Workflow step options available to users in Microsoft Dynamics CRM 4.0 is the wait condition.  This step basically puts a workflow operation on hold until a condition is met—for example, wait until five days after account was created, then send an email to the sales rep (that would be a timeout, by the way).  When the workflow condition is met, the workflow will resume.

Consider the following scenario—you create and publish a workflow with a wait condition, then before the workflow wait condition is met, you decide to modify something about that workflow.  In the example of the email to the sales rep, say you decide to make some changes to the email text.

It is not sufficient to just unpublish the workflow and modify it—this will change it for workflows that are generated after the change, but will not affect existing waiting workflow instances—a waiting workflow preserves the conditions and steps of the workflow as they were at the time that the workflow instance was created.  So when the wait time is up, the sales reps will receive your original email, not the updated text.

The answer is that you need to delete all the existing workflow instances.  Go to settings—>System Jobs in CRM, select the waiting workflow instances.  Click “More Actions” button and click “Cancel” (you can only delete completed/cancelled workflow instances), then delete the workflow instances.

This is pretty manageable if you have a small-medium number of waiting instances—even if you have 1,000 or more, you can pretty quickly cancel delete them, but you have to do it 250 at a time (since that’s the maximum number you can select from one page of a view).

But what about the extreme examples—for example you import 250,000 accounts, and now have 250,000 waiting workflow instances.  I don’t care how patient you are, it is not practical to delete this many waiting workflow instances 250 at a time.

In these extreme situations, you can delete the waiting workflow instances from the CRM SQL database.  Before you do this, a disclaimer—deleting from the SQL database is not supported by Microsoft.  Be sure to back up your database first.   I only recommend doing it in extreme situations (like this) where there are no other practical out of the box solutions.   For example, an asynchronous bulk delete job requires custom coding and takes too long to complete in these cases.

Waiting workflow records are stored in the workflowwaitsubscriptionbase, workflowlogbase, and asyncoperationbase tables.  When deleting these records, it is important that you do it in the correct order.

The following sql query will bulk delete waiting instances of a specific workflow.  Replace (insert name of workflow here) with the name of the workflow that generated the instances.

delete from workflowwaitsubscriptionbase

where asyncoperationid in

(select asyncoperationid from asyncoperationbase where name = '(insert name of workflow here)' and StateCode = 1)

delete from workflowlogbase

where asyncoperationid in

(select asyncoperationid from asyncoperationbase where name = '(insert name of workflow here)' and StateCode = 1)

delete from asyncoperationbase where name = '(insert name of workflow here)' and StateCode = 1

 

del.icio.us Tags: Microsoft CRM,Workflow,delete

Comments

[Mio Navman Spirit S300]

Well i would suggest better to reconfirm the details before deleting the data's from CRM so that we can avoid the various circumstances and not deleting the important one.

[8gb usb drive]

The answer is that you need to delete all the existing workflow instances. Go to settings—>System Jobs in CRM, select the waiting workflow instances. Click “More Actions” button and click “Cancel” (you can only delete completed/cancelled workflow instances), then delete the workflow instances.

[16gb usb stick]

May be this would work. SQL query will bulk delete waiting instances of a specific workflow.Replace with the name of the workflow that generated the instances.Delete from workflowwaitsubscriptionbase where asyncoperationid in
(select asyncoperationid from asyncoperationbase where name = '(insert name of workflow here)' and StateCode = 1).

[ugg boots]

Your writing is very elegant, very vivid and lively, I really like you, wish you continued to write

better articles, I will often try to concern, oh!

[Microsoft CRM Implementation]

Nice post on workflow instances.. thanks....