After a for each loop, Workflow errors when pausing
After a for each loop in a workflow, when the workflow enters any delay action the workflow errors. In the ULS logs, there are "Failed to persist workflow" errors.
Failed to persist workflow.
Batch the For Each loop to run through a smaller number of loops prior to having a delay. The general method for batching a for each loop is:
Add an inter variable "count" with default value 0.
In the For Each loop add a run if statement with a condition of count equals 24 (this batches for every 25 items)
In the run if statement place a set variable action configured to set count = 0 and a 5 minute pause.
After the run if statement, add a math operation action for count plus 1, and store the value back in to count.
The issue occurring is the amount of data that can be processed by SQL before it causes an error in persisting. For each loops can generate large amounts of data very quickly. This data all tries to persist at the delay action following the loop. By batching the for each loop the workflow has a smaller amount of data that needs to be processed for the persistence which allows the workflow to successfully complete.