Monday, August 6, 2012

CRM Workflows Stuck in a Waiting State

Why some Workflows is stuck in a waiting state and a Generic SQL error is logged.
I resently had an issue at one of our customers with workflows in Micorosoft Dynamics CRM 2011.
If we triggered multiple instances of a workflow some got stuck in a waiting state, not all.
If we triggered the workflow on one record it worked.
So you might be facing the same issue as i did.
If you open up the waiting workflow under system jobs in CRM 2011 you might see an error like the following.
Workflow paused due to error: Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Generic SQL error.Detail: 
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
  <ErrorCode>-2147204784</ErrorCode>
  <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
  <Message>Generic SQL error.</Message>
  <Timestamp>2012-06-15T08:47:45.7210596Z</Timestamp>
  <InnerFault>
    <ErrorCode>-2147204784</ErrorCode>
    <ErrorDetails xmlns:d3p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
    <Message>Generic SQL error.</Message>
    <Timestamp>2012-06-15T08:47:45.7220362Z</Timestamp>
    <InnerFault>
      <ErrorCode>-2147220970</ErrorCode>
      <ErrorDetails xmlns:d4p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
      <Message>System.Data.SqlClient.SqlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #E0E9194D</Message>
      <Timestamp>2012-06-15T08:47:45.7308256Z</Timestamp>
      <InnerFault i:nil="true" />
      <TraceText i:nil="true" />
    </InnerFault>
    <TraceText i:nil="true" />
  </InnerFault>
  <TraceText i:nil="true" />
</OrganizationServiceFault>
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Update(Entity entity, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType)
   at Microsoft.Crm.Extensibility.InprocessServiceProxy.UpdateCore(Entity entity)
   at Microsoft.Crm.Workflow.Services.UpdateActivityService.<>c__DisplayClass1.<UpdateInternal>b__0(IOrganizationService sdkService)
   at Microsoft.Crm.Workflow.Services.ActivityServiceBase.ExecuteInTransactedContext(ActivityDelegate activityDelegate)
   at Microsoft.Crm.Workflow.Services.UpdateActivityService.UpdateInternal(Entity entity, String stepId)
   at Microsoft.Crm.Workflow.Services.UpdateActivityService.ExecuteInternal(ActivityContext executionContext, UpdateEntity updateEntity)
   at Microsoft.Crm.Workflow.Services.UpdateActivityService.Execute(ActivityContext executionContext, UpdateEntity updateEntity)
   at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
This can error can be logged because of deadlock issues in the CRM organization database when you run multiple workflows at the same time.
You can run SQL Server profiler and trace locks to confirm that this is the reason for the error.
An excellent way to handle deadlock issues with Microsoft CRM is to activate the following for you CRM organization database.
SET READ_COMMITTED_SNAPSHOT ON
SET ALLOW_SNAPSHOT_ISOLATION ON

Make sure you have your SQL server tempdb on storage some extra space available for the tempdb because the tempdb will be used more by SQL server when you activate the above.

4 comments:

  1. @Hans: We are experiencing similar errors but a caveat in our case is that multiple instances of the workflow are also incrementing a field on a shared entity instance(think of it as each workflow instance is incrementing a counter). If we turn on the 2 settings you mention, wouldn't you run into "dirty reads" such that one instance reads a stale value and hence the counter will be off?

    ReplyDelete
  2. Hi.
    As i understand you won't have that issue.
    Pleas try it out in your test environment and also read this article to get a full understanding about the settings.
    http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx

    //Hans

    ReplyDelete
  3. sole large difference between your own 3 can be That physical therapy doesn't sole purchase inside sports related injuries or perhaps problems. Physical therapists will probably Choose to specialize with it is careers inside emphasis on factors such as pediatrics, geriatrics, AND ALSO neurology. other areas associated with expertise included with physical therapy usually are sports, cardiovascular sciences, IN ADDITION TO occupational therapy.Kyäni

    ReplyDelete
  4. Apartments within Alanya stay fairly cheep, in spite associated with service fees growing significantly. although your own market continues to be affected through recent financial events, It has proved for you to always be much additional robust when compared with in numerous various other countries. because the new laws came in Force allowing foreigners to help buy land, both call for AND fees have increased. Prices, particularly down your current coast, usually are rising rapidly, AS WELL AS tend to be essential to help carry on to help do thus through the after that all 5 to help ten years.It doesn't need make use of a estate agent to find AND ALSO buy a good Apartment throughout Alanya. However, a lot of consumers Select for you to do, so In the same way estate agents cater towards the focused Demands associated with foreigners AND ALSO usually are aware involving each of the legal requirements. sooner committing for you to procuring a Apartment, you need to settlement no matter whether There are almost any outstanding debts towards property. köpa hus i Alanya

    ReplyDelete