The Problem with Workflow History Records
In deployments with a decent amount of workflows that fire constantly (i.e., system jobs are spawned), the amount of records placed in the AsyncOperationBase table is quite impressive. However, when these system jobs complete (canceled or succeeded) they remain in this table until you decide to purge them. When these tables begin to grow into the millions of rows it imposes undue performance issues on the Async service and overall system performance may suffer as well. At that point you have a Database Administrator (DBA) issue to deal with. It is wise to tackle this issue up front, and consider it in your workflow designs and in your database maintenance processes.

Prior to Dynamics CRM 2011
Before the 2011 release, the only way to purge these records was to run a Structured Query Language (SQL) script either manually, or in a scheduled SQL Agent Job. The code for this script has been around for some time.
Read this Microsoft Knowledge Base (KB) article for this script along with other helpful background.

New Feature in Dynamics CRM 2011
During an upgrade from CRM 4.0 to 2011, the Deployment Manager Import Organization process will go through all system jobs and apply this purge script automatically to your database before completing the import/upgrade. Going forward, CRM 2011 contains a new feature that allows a workflow designer to define each workflow as available for what I call “auto-purge.” There is a check box on the Administration tab of the workflow design form at the bottom (shown in image above).

If you want to take advantage of this option, it can save considerable space and keep your performance issues to a minimum the more workflow you nominate for auto-purge.

Dynamics Workflow “Auto-Purge” Do’s and Don’ts
One thing to keep in mind when coding a workflow for auto-purge is only those system jobs that end in a status of “succeeded” will actually be purged. There is a mindset at play here…if a workflow ends with status of “canceled,” it may be worth investigating. With that in mind, I don’t suggest auto-purge those system jobs. As a workflow designer, desiring to take advantage of auto-purge, it is important to know this little tidbit of information. I have several clients who prefer to end system jobs as “canceled” if the check conditions cause the workflow to end without taking any action.

Therefore, we recommend the following Do’s and Don’ts:

  • DO consider the auto-purge option when creating any new workflow
  • DO consider rewriting existing workflows (that were upgraded from 4.0) to take advantage of auto-purge
  • DO end system jobs as “Succeeded” if check conditions produce a “no action” outcome in the workflow, if you want those to be auto-purged
  • DON’T assume this problem will manage itself
To find out more about this blog post or C5 Insight, please fill out our Contact form.