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.
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,