I need to develope a workflow for a customer and have the following use case:
- I have to create a site workflow which is supposed to run once per month (no problem so far)
- The workflow has to analyze a huge amount if list items (up to ~36000, this is where it get's tricky)
- The workflow should loop throguh the list and fetch some column values (easy)
- The column values are concatenated to create a some reports (easy)
So far so good. Problem is, I can't get my workflow to run on this amount of items. After some time the workflow always runs into an error ("[Workflowname] failed to start"). Additionally I always get a correlation ID, but funny thing is there is no ULS log entry with the specified correlation ID (probably micrososft thinks that would be to easy for me). I ran the different workflows at least 5 times and always receive the same useless error.
What I've tried so far:
1. Create 10 lists with 3000 items each and let the site workflow run over all of the lists
Result: The workflow runs over the first three lists and executes fine. After the third list, the above mentioned error occurs and the workflow stops (he remains "In Progress" but shows the error in the history and doesn't continue). So the workflow is running over 3 lists with 3000 items each (=9000 items) and stops afterwards.
2. Create a Site Workflow to Start another Site Workflow (5 times) that each only runs over one list
Result: Quite the same. The Main Workflow starts the other workflow properly for the first three or four times. But then i receive the same error again.
So I just cannot seem to get my Site Workflow to run over more than approx. 10000 items. Of course I could create one Site workflow per list, but in reality I will have up to 300 lists (one for each of the customers branch) and creating/maintaining 300 site workflows doesn't seem like a best practice to say the least.
I took a look at the SharePoint Workflow Tresholds on Software boundaries and limits for SharePoint 2013 but can't find anything like "max items queried in one workflow instance" (am I missing/misunderstanding something?). I know there is a "maximum loop threshold" you can set in the global nintex settings, but the explanation doesn't seem like this is the reason for the problem. Besides, even after deactivating this option completely I had the same error again. Safe looping is disabled as well.
This is what the workflow that actually queries the list items look like:
I'm using Query-List actions to get all item-IDs and another query-list action inside a for each action to receive the column values of each single item. I know this workflow doesn't look very useful, but this is only a dev version I'm testing on my local VM. Although the queried values are more meaningful on the customers environment, the basic workflow structure is the same.
Did any of you have this or a similar issue before? Is there anything I can do to let my site workflow run over this amount of items (>30k)? If anyone could also explain me why this is actually happening, I would be very grateful.
Any help/idea/comment is highly appreciated! If you need further information, please don't hesitate to ask!
Thanks in advance
update: I added a "Commit pending changes" action in the loop without any effect.