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.

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.

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


IF EXISTS (SELECT name from sys.indexes
WHERE name = N'CRM_AsyncOperation_CleanupCompleted')													
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

while(1=1)
begin
declare @DeleteRowCount int = 10000
declare @rowsAffected int
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
where 
-- 1: System Event, 9: Collect SQM data, 10: Workflow Operation, 12: Update Match Code, 25: Generate Full Text Catalog, 27: Update Contract States
  OperationType in (1, 9, 12, 25, 27, 10) 

-- 3:Completed
  AND StateCode = 3 

-- 30: Succeded, 32: Canceled
  AND StatusCode in (30, 32)

 select @rowsAffected = @@rowcount 
 delete poa from PrincipalObjectAccess poa 
   join WorkflowLogBase wlb on
    poa.ObjectId = wlb.WorkflowLogId
   join @DeletedAsyncRowsTable dart on
    wlb.AsyncOperationId = dart.AsyncOperationId
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where 
  W.AsyncOperationId = d.AsyncOperationId             
 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where 
  B.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d
where 
  O.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where 
  WS.AsyncOperationId = d.AsyncOperationID 
 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where 
  A.AsyncOperationId = d.AsyncOperationId
/*If not calling from a SQL job, use the WAITFOR DELAY*/
if(@DeleteRowCount > @rowsAffected)
  return
else
  WAITFOR DELAY '00:00:02.000'
end

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.