The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Managing size of AsyncOperationBase table in CRM 4.0

Managing size of AsyncOperationBase table in CRM 4.0

  • Comments 11

The asyncoperation entity is used in CRM 4.0 to manage various system jobs by the CRM Asynchronous Processing Host (MSCRMAsyncService) windows service. Over time this table accumulates large number of records leading to large databases. This is especially affects organizations that rely on asynchronous plug-ins and workflows. You can use the new Bulk Delete feature ( http://msdn.microsoft.com/en-us/library/cc155955.aspx) to manage the growth of records in the asyncoperation entity table. The bulk delete operation takes as input a QueryExpression and deletes the records returned by the query. There are some things to keep in mind when trying to delete asyncoperation records using bulk delete.

  • You need prvDelete privilege for asyncoperation entity. In a default installation, System Administrator role has this privilege.
  • You need prvBulkDelete privilege to call the BulkDelete API. In a default installation, System Administrator role has this privilege.
  • Only asyncoperation records in Completed state can be deleted.
  • If workflow type asyncoperations are deleted, you will lose history for some records.

In the example below, I have added a condition to select records that are completed for more than one month ago and are not workflow instances.

private static void DeleteCompletedAsyncOperationRecords(CrmService crmService)

{

QueryExpression expression = new QueryExpression(EntityName.asyncoperation.ToString());

expression.ColumnSet = new ColumnSet(new string[] { "asyncoperationid" });

expression.Criteria.AddCondition("statecode", ConditionOperator.Equal, (int)AsyncOperationState.Completed);

expression.Criteria.AddCondition("completedon", ConditionOperator.OlderThanXMonths, 1);

expression.Criteria.AddCondition("operationtype", ConditionOperator.NotEqual, (int)AsyncOperationType.Workflow);

Guid[] emptyRecipients = new Guid[0];

BulkDeleteRequest request = new BulkDeleteRequest();

request.JobName = "Bulk delete completed asyncoperations to free up space";

request.QuerySet = new QueryBase[] { expression };

request.ToRecipients = emptyRecipients;

request.CCRecipients = emptyRecipients;

request.SendEmailNotification = false;

request.RecurrencePattern = string.Empty;

request.StartDateTime = CrmDateTime.Now;

BulkDeleteResponse response = (BulkDeleteResponse)crmService.Execute(request);

Console.WriteLine("Bulk delete job id: {0}", response.JobId);

}

The bulk delete request is processed by MSCRMAsyncService in the background. Depending on the number of records returned by the query, the operation may take from minutes to hours to complete. You can monitor the status of the operation by selecting Settings àData Management à Bulk Record Deletion.

async2

The number of records deleted (and failed to delete) are tracked by the feature and displayed in the grid.

Async

The bulk delete operation can be scheduled as a recurring operation by setting the value of RecurrencePattern. If you plan to do so, I suggest that you run a non-recurring operation and wait for it to run to completion before creating the recurring operation. The AsyncOperationBase table contains thousands of records that would be deleted by the first time by the Bulk Delete operation. It may take several hours to delete the records because each record is deleted using a call to CRM SDK’s Delete method. Once the initial cleanup is done, you can create a recurring operation which should delete only a few records each time it runs. If you are considering adding a recurring system job to do the cleanup, I suggest a weekly frequency and running at off-peak usage times

request.RecurrencePattern = "FREQ=DAILY;INTERVAL=7;";

request.StartDateTime = CrmTypes.CreateCrmDateTimeFromUser(DateTime.Today.AddDays(1)); // start at midnight tomorrow

You may not see an immediate change in the database size. The records are physically deleted from the AsyncOperationBase table by DeletionService, which runs once a day. After the records are physically deleted by DeletionService, you may need to run “DBCC SHRINKDATABASE” against the organization database to see the actual space usage.

Cheers,

Mahesh Vijayaraghavan

  • I have a couple of issues with this solution.  1) We have a million rows in the asyncoperationbase table and web services are terribly slow.

    2) The records do not get deleted, they just get flagged for deletion and the deletion service has no effect.

    Is there another option?

  • As per gobobio our deletion service also does not delete the Records there is 3gb of data on this table.

  • When too much records is to be deleted then deletion service fails on SQL general timeouts.

    You have to use SQL manager to delete those records manually. Select records with DELETIONSTAGE=2 and then you need to delete first n records and repeat and repeat...

    The n depends on SQL server settings and overall server performance.

    so the SQL statement should look like (sorry for accuracy - I am out of CRM server)

    DELETE top 5000 asyncoperation where deletionstage=2

  • New to Microsoft Dynamics CRM 4 is the concept of having a single windows service that will manage all

  • New to Microsoft Dynamics CRM 4 is the concept of having a single windows service that will manage all

  • New to Microsoft Dynamics CRM 4 is the concept of having a single windows service that will manage all

  • gobobio: The records are deleted by DeletionService which is scheduled to run once a day. You can change the frequency using <a href="http://code.msdn.microsoft.com/ScaleGroupJobEditor">CRM ScaleGroup Job Editor tool</a>.

    silver100: The DeletionService job may fail but the query to delete the record quite possibly continues to execute on the server. On the other hand, if the query fails due to lack of log space, deleting a small number of records may be the only solution. If you are running into this problem regularly, I suggest opening a Support ticket.

  • request.StartDateTime = CrmTypes.CreateCrmDateTimeFromUser(DateTime.Today.AddDays(1)); // start at midnight tomorrow

    won´t work CrmTypes. is not defined

    Please publish a working example for recurring bulk delete job or offer a download sample from codeplex or MSDN Code Gallery

  • recently got it working on the production environment, however, for certain record, it failed to delete.

    Thanks.

    error code: 0x80040216

    error message: An error has occurred. Try this action again. If the problem continues, check the Microsoft Dynamics CRM Community for solutions or contact your organization's Microsoft Dynamics CRM Administrator. Finally, you can contact Microsoft Support.

  • Carsten: CrmTypes class is defined in Microsoft.Crm.SdkTypeProxy.dll

    please see Sean's post for code.

    http://blogs.msdn.com/crm/archive/2008/11/13/leveraging-bulk-delete-jobs-to-manage-system-job-log-records.aspx

    poster who got error code: 0x80040216:

    If you enable tracing, it should show more error details.

  • When I set the recurrence pattern to "Freq=Daily; Interval=7", the record from the UI still says Recurrence=No and Next Run is empty (this is after the job has completed). Also, there are multiple taskeng.exe in the task manager. Please advice.

Page 1 of 1 (11 items)
Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post