CRM 4.0. Random generic SQL error when a workflow runs

すべての返信

• 2011年12月14日 16:37

   

   
  

  0

  Is the workflow only using standard activities or also custom ones?

  The reason of the error can be either a bug in a custom activity, where the platform doesn't provide proper error handling or a simple timeout on the database. Timeouts might happen if the system is overloaded. Workflows are quite sensitive. Putting the Async Service on a separate machine might help.

  
  

  • 編集済み kowgli 2011年12月14日 16:38
  •  
  • 返信
  • 引用

   
• 2011年12月19日 15:39

   

   
  

  0

  Thanks Kowgli.

  There are no custom activities/entities referenced in the workflow. I have discovered that you can 'resume' the workflow jobs and they will complete sucessfully.

  This system has 150 workflow jobs running and in my opinion is the main reason for this error.

  • 返信
  • 引用

   
• 2011年12月19日 17:07

  モデレータ

   

   
  

  0

  It is quite likely that the error comes from SQL deadlocks. The asynchronous service deliberately sets some connections to have a low deadlock priority, which means they would be more likely to be cancelled if a deadlock occurs.

  If you enable tracing in CRM, then you should get more detail on what's causing the error

  ---

  Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
   

  • 編集済み DavidJennawayMVP, Moderator 2011年12月19日 17:07
  •  
  • 返信
  • 引用

   
• 2011年12月19日 21:00

   

   
  

  0
  can you post the rules of the workflow so we can see what you are asking it to do?

  ---

  Patrick Kitchell MS Business Professional CRM Manager
  • 返信
  • 引用

   
• 2011年12月20日 12:26

   

   
  

  0

  Patrick. I could post them however it affects more than one workflow and i deduce from that that's it's not to do with the construction of the workflow itself and more to do with a wider issue.

  If you'd still like to see the steps of one of the workflows then let me know.

  Thanks, david

  • 返信
  • 引用

   
• 2011年12月20日 12:30

   

   
  

  0

  I have seen these errors also on our solution when a plugin is failing or if the fields that a workflow is calling has changed in type or been removed.

   

  Patrick

  ---

  Patrick Kitchell MS Business Professional CRM Manager
  • 返信
  • 引用

   
• 2011年12月20日 13:50

   

   
  

  0

  Patrick, thanks. i doubt very much if this is the case, the majority of workflows actually complete successfully and it's just an occasional one that fails and is left in a 'waiting' state. these can be resumed and usually then complete.

  It has to be more environmental. I have logged it with Microsoft who are also struggling to pin-point the cause.

  If there is an outcome i shall post it here.

  Thanks, david

  • 返信
  • 引用

   
• 2011年12月21日 16:21

   

   
  

  0
  Patrick , I'm very curious to what you find out.  We are experiencing the same error with all workflows.  Our workflows are very simple and some are only updating one field on a case.  Look forward to see what you find and I will share the same as we discover anything.
  • 返信
  • 引用

   
• 2012年1月11日 9:09

   

   
  

  1
  At the moment Microsoft are telling me it's an issue with the amount of workflows that are running, in this particular system many workflows can be triggered in an instant and it would appear that SQL cannot handle the volume. I have increased the time-out settings but this has made little difference, the next step is to look into how better to optimise the system including redesigning some of the workflows.
  • 返信
  • 引用

   
• 2012年3月26日 15:39

   

   
  

  0

  This issue still persists. I've had a call logged with Microsoft since 2011, initially they gave the reason as too many workflow instances triggering in close proximity and that the resources [SQL] couldn't cope. Since this i have discovered another 2 identical issues, one on CRM 4 and the other on a 2011 system, neither of these systems have many workflows running and yet they still randomly fail citing an SQL error.

  It's becoming very frustrating as Microsoft, who are 'the last line of defence' are struggling to assist at the moment. This issue is impacting on clients in several ways including financially and i feel somewhat impotent that i cannot progress this in any shape or form.

  In all these systems CRM & SQL sit on the same box. If we knew that moving SQL Server to its own server or moving the Async Service would resolve this then it would be worthwhile, as we are, we're unsure and it would be a costly move to make to find out that it hadn't resolved the issue.

  I find it hard to believe that others in the community haven't experienced this or a similar issue.

  If anyone feels they can offer assistance it would be greatly appreciated.

  Many thanks, David

  • 返信
  • 引用

   
• 2012年5月7日 17:02

   

   
  

  0

  We do have the same issue, and we have thousands of workflows running in our system.

  Like you said, completly random.

  I'm in the process of setting tracer for CRM and maybe we can track down the real issue.

  If anyone got idea or solutions, please let us know.

  • 返信
  • 引用

   
• 2012年6月6日 12:02

   

   
  

  0

  Dear all,

  we are having the same issue with several workflows on MS-own entities. Would be kind to get a resolution of this Problem.

  Best regards

  MISUMI

  • 返信
  • 引用

   
• 2012年6月6日 12:17

  モデレータ

   

   
  

  0

  This is a common proble, specially when you have several workflows executing at the same time on the same tables. There is a white paper from Microsoft that usggests enabling RCSI in SQL database to avoid (or reduce) this error: http://www.microsoft.com/en-us/download/details.aspx?id=3997

  It might not be the best solution, but there is little you can do to avoid SQL deadlocks since all database access is crontroled by the CRM platform and the Async Service.

  You could also try contacting MS support.

  ---

  Gonzalo | gonzaloruizcrm.blogspot.com

  • 返信
  • 引用