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


Comments (1)

  1. Hi all, We regularly receive questions about how to get rid of many workflow instances stuck in Waiting

Skip to main content