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?
- 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.
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.
IF EXISTS (SELECT name from sys.indexes
WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
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
-- 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)
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
W.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
B.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d
O.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
WS.AsyncOperationId = d.AsyncOperationID
delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
A.AsyncOperationId = d.AsyncOperationId
/*If not calling from a SQL job, use the WAITFOR DELAY*/
if(@DeleteRowCount > @rowsAffected)
WAITFOR DELAY '00:00:02.000'
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.