SDK Cleanup script for unwanted Workflow Instances stuck in Waiting… state

Hi all,

We regularly receive questions about how to get rid of many workflow instances stuck in Waiting state. 
You can cancel these workflow within the UI but for some reason this is sometime not suitable and executing a script is better.
Once these Workflow instances are canceled, you might need to perform a cleanup of the MSCRM Database.

Here is the code based on the SDK documentation my colleague Fouad Rachkidi (CRM Support Escalation Engineer) wrote:

    1: /*
    2: Microsoft provides programming examples for illustration only without warranty either expressed or implied.
    3: This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose
    4: This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures 
    5: Microsoft support engineers can help explain the functionality of a particular procedure. 
    6: However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements
    7: 
    8: You should install CRM 4.0 Rollup 3
    9: Also enable the registry keys part of KB 968755
   10: http://support.microsoft.com/kb/968755
   11: */
   12:  
   13: using System;
   14: using System.Collections.Generic;
   15: using System.Text;
   16: using System.Web.Services.Protocols;
   17: using CleanAsyncConApp.CrmSdk;
   18:  
   19: namespace CleanAsyncConApp
   20: {
   21:     class Program
   22:     {
   23:         static void Main(string[] args)
   24:         {
   25:             try
   26:             {
   27:                 CrmAuthenticationToken token = new CrmAuthenticationToken();
   28:                 token.AuthenticationType = 0; //AD authentication
   29:                 token.OrganizationName = "DIC"; //Organization Name
   30:  
   31:                 CrmService service = new CrmService();
   32:                 service.CrmAuthenticationTokenValue = token;
   33:                 service.Credentials = System.Net.CredentialCache.DefaultCredentials;
   34:                 service.UnsafeAuthenticatedConnectionSharing = true;
   35:                 service.Timeout = 3600000;
   36:                 
   37:                 ConditionExpression operationTypeCondition = new ConditionExpression();
   38:                 operationTypeCondition.AttributeName = "operationtype";
   39:                 operationTypeCondition.Operator = ConditionOperator.Equal;
   40:                 operationTypeCondition.Values = new object[1];
   41:                 operationTypeCondition.Values[0] = 10; //Workflow
   42:  
   43:                 ConditionExpression statusCodeCondition = new ConditionExpression();
   44:                 statusCodeCondition.AttributeName = "statuscode";
   45:                 statusCodeCondition.Operator = ConditionOperator.Equal;
   46:                 statusCodeCondition.Values = new object[1];
   47:                 statusCodeCondition.Values[0] = 10; //Waiting
   48:  
   49:                 FilterExpression queryFilter = new FilterExpression();
   50:                 queryFilter.FilterOperator = LogicalOperator.And;
   51:                 queryFilter.Conditions = 
   52:                     new ConditionExpression[] {operationTypeCondition, statusCodeCondition };
   53:  
   54:                 ColumnSet queryColumns = new ColumnSet();
   55:                 queryColumns.Attributes = 
   56:                     new string[] { "asyncoperationid", "operationtype", "statuscode"};
   57:  
   58:                 QueryExpression query = new QueryExpression();
   59:                 query.EntityName = EntityName.asyncoperation.ToString();
   60:                 query.Criteria = queryFilter;
   61:                 query.ColumnSet = queryColumns;
   62:                 query.PageInfo = new PagingInfo();
   63:                 query.PageInfo.PageNumber = 1;
   64:                 query.PageInfo.Count = 50;
   65:                 Console.WriteLine("CRM query created");
   66:  
   67:                 BusinessEntityCollection bec = service.RetrieveMultiple(query);
   68:                 Console.WriteLine("CRM query executed");
   69:                 Int64 counter = 0;
   70:  
   71:                 if (bec.BusinessEntities.Length > 0)
   72:                 {
   73:                     do
   74:                     {
   75:                         foreach (BusinessEntity be in bec.BusinessEntities)
   76:                         {
   77:                             asyncoperation async = (asyncoperation)be;
   78:                             async.statecode = new AsyncOperationStateInfo();
   79:                             async.statecode.Value = AsyncOperationState.Completed;
   80:                             service.Update(async);
   81:                             counter += 1;
   82:                         }
   83:                         query.PageInfo.PageNumber += 1;
   84:                         query.PageInfo.PagingCookie = bec.PagingCookie;
   85:                         bec = service.RetrieveMultiple(query);
   86:                     } while (bec.MoreRecords);
   87:                 }
   88:                 else
   89:                 {
   90:                     Console.WriteLine("No workflows found with status waiting");
   91:                 }
   92:  
   93:                 if (counter != 0)
   94:                 {
   95:                     Console.WriteLine(counter + 
   96:                         " workflows have been updated with a state code completed");
   97:                 }
   98:             }
   99:             catch (SoapException soapex)
  100:             {
  101:                 Console.WriteLine(soapex.Detail.InnerText);
  102:             }
  103:             catch (Exception ex)
  104:             {
  105:                 Console.WriteLine(ex.Message);
  106:             }
  107:         }
  108:     }
  109: }

Once these Workflow status will change, you can clean the CRM MSCRM Database using the following script:

968520    Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM 4.0

http://support.microsoft.com/default.aspx?scid=kb;EN-US;968520

More information:

AsyncOperationState Enumeration (CrmService)

http://msdn.microsoft.com/en-us/library/bb890231.aspx

Hope this helps.

Benjamin LECOQ