Long running workflows and partial PS database cleanup

Migrateduser

Hi,

We have some long running workflows of up to 8 months. At a point the workflow could enter a stage where it checks a certain condition (such as whether information has been received in Content Server). This check consist of a web service call, an evaluation step and a delay step. The delay step varies from a few minutes to check if a generated document is available in the DMS to up to a day when the workflow must wait for at least 5 weeks to check whether a request has been fulfilled. Each check adds 3 work items in the database which is normally not an issue given that each process instance on average performs 20 checks. However, there could be thousands of these process instances running at the same time (of course not all of them are actively checking for conditions). We see the database filling up. Cleaning up the database by a miner job is not yet an option because we need the workflow history for about 7 years for leag and follow-on processes that could be started. For example, in the 7 year period someone could request a change which we need to able to process and then the workflow history of previous instances comes in very handy. We found that the information that we can collect using a miner job in BAM not sufficient for our purpose. Reducing the number of checks with delay escalation is not an option because the workflow should continue as soon a condition has been met. Drastically changing the workflow is also not favorable because we already have been in production for 3 years now.

Given the above I have 2 questions:

1. Could the database size and number of running instances cause a situation where it takes a long time before PS is available?
2. Can we partially clean up de PS database by removing the intermediate systems steps of a check cycle (but keeping the first and the last), i.e. by running an SQL cleanup script directly on the database

I know the second step could be potentially dangerous and we will make sure that we have restore options in place when things go wrong.

Best regards,

Marc