·
4 min read

Cleaning up records from the AsyncOperationBase / WorkflowLogBase table

Hi all,

When an async workflow is triggered in your CDS organization, a record will be created in the AsyncOperationBase table to track the processing of the async job. Additional records will also be created in the WorkflowLogBase table to maintain logs for the workflow execution.

Business Process Flows also store BPF stage transition and action logs for the BPF in the WorkflowLogBase table.

If your organization has heavy use of workflows or business process flows, these table will grow over time and eventually become large enough to introduce performance issues as well as consume excessive storage in your organization database.

You can leverage the bulk delete system jobs to delete unneeded records from these tables. Navigate to ‘Settings’ -> ‘Data Management’ -> ‘Bulk Record Deletion’ to view the bulk delete system jobs.
Leveraging out of the box system bulk delete jobs

From the Bulk Record Deletion grid, you can use the view selector to view the completed, in-progress, pending and recurring bulk deletion system jobs. In the below image, I have selected the ‘Recurring Bulk Deletion System Jobs’ view which shows the job definitions for the 3 bulk deletion system jobs we include out of the box.

 

Bulk record deletion

 

If you open one of these recurring bulk deletion system job records, you can see the query the job uses to identify which records to delete as well as the schedule the job runs on. For these out of the box system jobs, you cannot modify the query used by the system job, but you can modify the schedule the job runs on. If you update the view selector to show jobs that have already been scheduled, in-progress or executed, you can cancel / resume / postpone the job. You can find these options in the ‘Actions’ menu if you open the record.

Postpone option in Actions menu


Create your own bulk delete jobs

If the out of the box system bulk delete jobs do not meet your organization’s needs, you can create your own bulk delete job. From the Bulk Record Deletion grid, click the ‘New’ command bar button. This will open a wizard that allows you to define a query for the records you want deleted. The wizard also provides the ability to preview the set of records the query will pick up for deletion to allow you to test that you have constructed your query correctly.

To clean up workflow records from the AsyncOperationBase table you will need to select the ‘System Jobs’ entity and select ‘[new]’ in the view selector to indicate you want to create your own query. You can only delete completed workflows. Workflows waiting to run or currently in progress cannot and should not be clean up by your system job.

Add the following conditions to your query:

  • ‘System Job Type’ equals ‘workflow’ — target workflow records
  • ‘Status’ equals ‘completed’ — only completed workflows are valid to complete
  • [Optional] filter on the StatusCodes (succeeded / failed / canceled) that are valid for completed StateCode
  • [Optional] filter on ‘completed on’ field to only delete older workflows
  • [Optional] Any additional filters you wish to apply

 

Define search criteria

 

The next page of the wizard will allow you to set the frequency your bulk delete job will run at. You can create a one-time bulk deletion job or define a schedule to allow your job to run at set intervals.

 

Set the bulk delete job frequency

 

How to run synchronous bulk delete jobs

For bulk deletion of workflow system job records, you also have the option of performing a synchronous bulk delete of the records by selecting the ‘Immediately’ radio button option. This delete is performed with direct SQL execution rather than passing each record through the delete event pipeline which results in a large performance gain. This is a great option if you want to quickly clean up the extra workflow records and not have your bulk delete job wait in the async queue for processing.

The ‘Immediately’ radio button will be enabled if the following criteria are met:

  1. Bulk delete job is for entity ‘System Jobs’
  2. Search Criteria has the condition ‘system job type’ equals ‘workflow’
  3. User creating the bulk delete job has global depth for the delete privilege on the AsyncOperation entity (System Administrator security role is one of the roles that has this privilege).

Synchronous bulk delete will only delete AsyncOperation records in the completed state. A maximum of one million records each invocation. You will need to perform the delete multiple times if you have more than one million records you want to clean up.

Best practices for designing workflows

Once you have deleted the unneeded records in your workflow tables, there are a few steps you can take in your workflow design to prevent the tables from growing as fast in the future.

For asynchronous workflows, it is recommended to check the ‘Automatically delete completed workflow jobs (to save disk space)’ checkbox in the workflow editor. Checking this box will allow the system to delete workflow logs for successful executions to save space. Logs from failed workflow executions will always be saved for troubleshooting.

 

Workflow job retention

 

For synchronous workflows, it is recommended to check the ‘Keep logs for workflow jobs that encountered errors’ checkbox in the workflow editor. Checking this box will allow logs from failed workflow executions to be saved for troubleshooting. Logs from successful workflow executions will always be deleted to save space.

 

Workflow log retention

 

 

Additional Resources:

https://support.microsoft.com/en-us/help/968520/performance-is-slow-if-the-asyncoperationbase-table-becomes-too-large

 

– Hampton Terry