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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
-- 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.
