The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Purging old instances of workflow in Microsoft CRM

Purging old instances of workflow in Microsoft CRM

  • Comments 3

CRM by default does not provide a good story for purging old workflow process instances and other workflow tables at regular basis. As the system ages and if there are multiple looping rules present in the system, you will hit a time when workflow rules will start failing due to SQL timeouts. The way out is to purge the workflow process instances at a regular basis. Say one once a month.

 

The best way to purge the process instances is to have them marked as deleted.. and let the deletion service delete the instances. When the deletion service cleans up the wf process instance table it will clean up the 7 or more other workflow tables too.. i.e it will delete all the wf rules instances that are no more associated with any wf process instance. Note that deletion service will kick in only once every 2 hours.

 

NB: Due care and attention should be taken with all direct updates of SQL as they fall outside the supportability of CRM.

 

To delete wf process instance you can have a script in two parts.

1) delete all except the ones on the opportunity.

 

update wfprocessinstance

set deletionstatecode = 2

where objecttypecode != 3 -- this is for opporutunity.

and currentstepId = null

and

( statecode = 4  -- for completed rules.

or statecode = 3)  -- for canceled rules.

and lastmodifiedon > startdate

and lastmodifiedon < enddate

 

You can have the start and end date defined based on your criteria, as to how often and how old wf rule & logs you want to delete. 

 

2) Since the opportunity can have sales process on which the sales pipeline report is based on, you may want to have a different start and end date time for it. Else you can use the above query without the objecttypecode condition.

 

To get the wfprocess instance that are related to sales process, join the two tables wfprocess and wfprocess instance and check the process type code to be equal to -2 for salesprocess instances and -1 for regular wf rule instances.

 

update wfprocessinstance

set deletioncode = 1

where processinstanceid in

(

select wpi.processinstanceid from wfprocess wp, wfprocessinstance wpi

where

( wp.processtypecode = -1  -- for regular wf process instances.

and wpi.currentstepId = null

and

( statecode = 4  -- for completed rules.

or statecode = 3)  -- for canceled rules.

and lastmodifiedon > startdate

and lastmodifiedon < enddate.

)

and

(

( wp.processtypecode = -2  -- for sales process instances.

and wpi.currentstepId = null

and

( statecode = 4  -- for completed rules.

or statecode = 3)  -- for canceled rules.

and lastmodifiedon > startdate

and lastmodifiedon < enddate

)

)

 

Note: Workflow rules are the definitions or construct, what the rule looks like and the rule instances are copies of this construct to which the actual wf process instances are linked and executed.

 

regards,

Shashi Ranjan
  • You wrote: "As the system ages and if there are multiple looping rules present in the system, you will hit a time when workflow rules will start failing due to SQL timeouts."

    Can you expand on this a little more? I have a lot of CRM deployments that use multiple looping rules. How do I identify which rules are failing, why they are failing, and when they are failing? Is having multiple looping WF rules not a reliable scenario?

  • Looping rules are reliable as long as you keep the table size from growing very very large.

  • Thanks very much for this post - I have a client who never purged any workflows and now has 1.5 million old workfow process instances in CRM 1.2.  

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post