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: https://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
https://support.microsoft.com/default.aspx?scid=kb;EN-US;968520
More information:
AsyncOperationState Enumeration (CrmService)
https://msdn.microsoft.com/en-us/library/bb890231.aspx
Hope this helps.
Benjamin LECOQ