Delete process instance by SQL

We are using Flowable 6.8.0 in production with over 70 mln process instances. We have one process instance which is problematic. Because of mistake the process was in the loop for two weeks. It have created a process instance with over 100.000 activities(act_ru_actinst).

We can’t delete this problematic proces by runtimeService.deleteProcess, after 10h there is not progress (timeout from database)

We have added some kind of loop watcher which breaks process after reaching 10.000 activities to avoid problems in future

But now how to delete this problematic process. Do you have some kind of SQL’s which we have to execute in specyfic order?

If you delete the process instance in the same way as flowable does, I would expect the same result - timeout.
I would delete process instance in the steps, each in a separate transaction.

  1. delete all activities in act_ru_actinst related to this process. ( used only to show path for runtime processes)
  2. try to end the process. (move execution outside of the loop …)
  3. (Optional if you need to delete the process from history too): try to delete the process with flowable api (e.g.historyService.createHistoricProcessInstanceQuery().processInstanceId(...).delete(). If operation fails on timeout try to delete process instance in separate transactions. (HistoricActivityInstances, HistoriTaskInstances, historicProcessInstance).


Perhaps this issue could help?