Leveraging bulk delete jobs to manage System Job log records

New to Microsoft Dynamics CRM 4 is the concept of having a single windows service that will manage all asynchronous operations. Each time an asynchronous operation takes place a number of log entries are created in the tables that support asynchronous operations.

Some examples of asynchronous operations are:

• Workflow tasks
• Asynchronous plugins
• MatchCode operations (used for duplicate detection)
• Maintenance activities

The logged information is great for tracking system jobs and workflows but can contribute to a large CRM organization database. For instance, every 5 minutes CRM generates matchcodes for duplicate detection rules to keep duplicate detection current. A log record is created for each one of the entities, if you had 3 entities with duplicate detection rules there would be 864 matchcode log entries per day (1440 minutes / 5 minutes * 3).

So, now you’re asking, “what can I do to control this or clear these out?”. You can use the Bulk Delete feature ( http://msdn.microsoft.com/en-us/library/cc155955.aspx) as documented in the CRM SDK. You can issue bulk deletes for out of the box and custom entities which includes the AsyncOperation entity otherwise known as the “System Job” entity. The bulk delete operation takes as input a QueryExpression and deletes the records returned by the query. Any QueryExpression you write could be used as part of a bulk delete. After creating the bulk delete job CRM will execute the deletes one after the other (the deletes are not set based) each delete will be evaluated against the business logic in the system just as if you were deleting records in the application. This means that any plugins you’ve registered will fire, cascading will occur, etc. It also means that the delete jobs may take some time to process before all the records are cleared out.

There are some prerequisites when trying to delete asyncoperation records using bulk delete.

• User must be a system administrator – OR – hold the prvDelete privilege for asyncoperation entity as well as the prvBulkDelete privilege to call the BulkDelete API
• Only asyncoperation records in Completed state can be deleted
• If workflow type asyncoperations are deleted, you will lose workflow history for some records

Keeping those items in mind we should only include completed records and for this example we’ll also exclude any workflow operations. The sample can be altered to specifically delete match code jobs or include workflow operations, the sky is the limit. However, be careful with this sample be sure to test your bulk delete in an environment where you can monitor the deleted data, you should test your QueryExpression before issuing a BulkDelete to be sure that it is returning the correct data. A fully functioning solution / sample can be found at: http://code.msdn.microsoft.com/crmbulkdelete.

The following sample builds off the article written by Mahesh (link). In my sample I’ve included the following helper files provided in the CRM SDK:

• businessentitypartialtypes.cs
• columnscollection.cs
• columnsethelper.cs
• conditionexpressionhelper.cs
• conditionexpressionhelpercollection.cs
• enums.cs
• filterexpressionhelper.cs
• filterexpressionhelpercollection.cs
• linkentityhelper.cs
• linkentityhelpercollection.cs
• orderexpressioncollection.cs
• queryexpressionhelper.cs

Please note that when using these helpers you’ll want to make sure you correct the namespaces in the helper files, for instance if your project namespace is BulkDeleteMessageSample and web service reference is CrmSdk; the namespaces in the helpers should be changed to BulkDeleteMessageSample.CrmSdk.

   1: static void runBulkDelete()

   2: {

   3:  

   4:     CrmAuthenticationToken token = new CrmAuthenticationToken();

   5:     token.AuthenticationType = 0;

   6:     token.OrganizationName = "AdventureWorksCycle";

   7:     CrmService service = new CrmService();

   8:     service.Url = "http://crmserver/mscrmservices/2007/crmservice.asmx";

   9:     service.CrmAuthenticationTokenValue = token;

  10:     service.Credentials = System.Net.CredentialCache.DefaultCredentials;

  11:     //create a QueryExpression using the helper

  12:     QueryExpressionHelper expression = new QueryExpressionHelper("asyncoperation");

  13:     expression.Columns.AddColumn("asyncoperationid"); 

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

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

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

  17:     Guid[] emptyRecipients = new Guid[0];

  18:     //Create a BulkDeleteRequest 

  19:     BulkDeleteRequest request = new BulkDeleteRequest();

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

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

  22:     request.ToRecipients = emptyRecipients;

  23:     request.CCRecipients = emptyRecipients;

  24:     request.SendEmailNotification = false;

  25:     request.RecurrencePattern = string.Empty;

  26:     request.StartDateTime = CrmDateTime.Now;

  27:     BulkDeleteResponse response = (BulkDeleteResponse)service.Execute(request);

  28:     Console.WriteLine("Bulk delete job with id: {0} has been created", response.JobId);

  29: }

Additional notes regarding Bulk Delete jobs:

• System Setup to prevent timeouts: After running a bulk delete job you may notice events in your event log from the source “MSCRMAsyncService” and an error message of: System.Data.SqlClient.SqlException: Timeout expired. When running your first bulk delete job increase the DWORD OleDBTimeout value to 300 (decimal) in the HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM key. NOTE: be sure to return this value to its original value after the deletion completes, if the key did not exist prior to this you can set it to 30 (decimal).
• Recurrence, you could run this job weekly by setting the RecurrencePattern in accordance with the SDK (link) a weekly recurrence would be “FREQ=Weekly;INTERVAL=1”. You can also control the start time of the job by setting the StartDateTime. For a one time operation set the StartDateTime and leave the RecurrencePattern empty .
• Email notification: You can setup an array of Guid’s, the Guid’s should contain SystemUserId’s of users you wish to have emailed after the job completes. To set the operation up to email when completing the job you should setup:
• Set a value of the requests ToRecipients (must be a of type Guid[])
• Set the value of SendEmailNotification to True (if set to false an email will not be sent)
• Optionally, you may set a list of CCRecipients (also of type Guid[])
• Database size: when the bulk delete process runs records will get marked for deletion; within 24 hours those records will get hard deleted from the database. You will not see a reduction in the database size as SQL will leave the empty space in the database. After the bulk deletes and deletion jobs run you can then shrink the database to recover the space on disk.
• Finding your Bulk Delete Job: After running the above code sample you can find your bulk delete job in CRM under Settings | Data Management | Bulk Record Deletion. If you have set the job as a recurring job you can edit the recurrence settings through the UI in the Bulk Record Deletion screen (see image below).

Cheers,

Sean McNellis