Dynamics CRM, Performance

AsyncOperationBase table becomes larger in Microsoft Dynamics CRM

If you check your async table, you might notice, that the table is getting bigger and bigger. But why is this getting bigger?

There are several things to remember.

Each background operation is saved as an asynchronous operations. But what are background operations?

Some examples:

  • Asynchronous Workflows
  • Bulk deletes
  • System Events

 

But also operations, that run in the background without the notice of the user like:

  • Collect SQM data
  • Update Match Codes
  • Generate Full Text Catalog
  • Update Contract States

 

All these operations saves data in the table, i.e. which entity and with entry (id) is processed, at which time the operation finished, who is the owner of the task and many more.

 

If the task is process successfully, the information is not automatically removed from the table. This has to be configured or done manually.

 

Workflows:

While creating or modifying a workflow, there is an option called “Automatically delete completed workflow jobs (to save disk space)”. This option tells the background job, which is executing the workflow, that the result should be deleted, if the workflow is processed successfully.

Automatically delete completed workflow jobs
Automatically delete completed workflow jobs (to save disk space)

Detailed information can also be found in the blogpost of Powerobjects.

This only helps, if this are workflows.

Systemjobs and the rest

But if these are system jobs, you have to use some manual functions.

Microsoft itself provides a script, which cleans the table. The original script can be found the the following page: https://support.microsoft.com/en-us/help/968520/performance-is-slow-if-the-asyncoperationbase-table-becomes-too-large

I added some comments to the script, to get a better understanding of what is deleted.

 

Microsoft provides this script and also the support engineers will send you this link, if you open a ticket, having performance issues with your database.

 

You can add this script in the sql scheduler to be executed every day or once a week and as the Microsoft support engineer provided these script, it should also be supported.

 

But if you like to do this in CRM, it’s also possible. Just create a new “Bulk Delete”-Job and select the values like in the script. The job will look like that one in the screenshot below. Set a recurrence and you have the same result as executing the script.

Bulk Delete Async Operations