Occasionally and completely random a workflow will error with the following message:
A SQL server error occurred. Try this action again. If the problem continues check the Microsoft dynamics community for a solution or contact your organisations Microsoft dynamics CRM administrator. Finally you can contact microsoft support.
The error is happening in more than one workflow so i can rule out it being specific to a particular workflow job. It's a very generic error and i'm looking for advise on how best to troubleshoot it.
I may well end up escalating it to Microsoft but though i'd check the community first.
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
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.
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:00can 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
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.
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 Kitchell MS Business Professional CRM Manager
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.
2011年12月21日 16:21Patrick , 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:09At 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.
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
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.
we are having the same issue with several workflows on MS-own entities. Would be kind to get a resolution of this Problem.
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