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.
set deletionstatecode = 2
where objecttypecode != 3 -- this is for opporutunity.
and currentstepId = null
( 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.
set deletioncode = 1
where processinstanceid in
select wpi.processinstanceid from wfprocess wp, wfprocessinstance wpi
( wp.processtypecode = -1 -- for regular wf process instances.
and wpi.currentstepId = null
and lastmodifiedon < enddate.
( wp.processtypecode = -2 -- for sales process instances.
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.
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.