To prevent disk-space issues and long waits in Process Portal, use the LSW_BPD_INSTANCE_DELETE stored procedure to remove all runtime data in the Process Server database that is associated with a completed business process definition (BPD) instance.
Run the query during an off period or maintenance window. When thousands of instances and data are purged, this process might cause a strain on the LSW_TASK and LSW_BPD_INSTANCE tables, which are core product tables. Running a clean-up job outside of normal business hours is a good practice.
If you want to archive data rather than deleting it, copy the data into a custom-built table outside of the product schema.
This task is the manual way to perform the same removal of process instances as the BPMProcessInstancesCleanup command.
Completed BPD instances are not deleted from the system automatically. After a process instance is completed, the instance is typically no longer needed, so it can be removed from the Process Server database. Use the LSW_BPD_INSTANCE_DELETE stored procedure to delete old instances.
When an instance completes and all of its associated tasks are closed, future work is not possible with the instance. You cannot restart it, assign it to someone, or edit old work. When a user logs in to Process Portal, various tables are queried to gather data on the active tasks for that user. The operation involves full table scans, so even if only 35% of the data is relevant, it is going to take a while to pull the tasks needed for the user. If the other 65% is deleted, there is less data to scan.
If you do not delete the old completed instances, your team experiences slow performance on Process Portal and a potentially unusable state. Ignoring increases in database size cause an increase in backup time and disk space.
Deleting old instances affects only the search for history items from the Process Portal inbox. When you run the delete queries, you can specify to delete only completed tasks that are older than 30 days. Store any data that you really need in either the performance database or in another system of record for auditing or metrics.
You can use the stored procedure to safely clean up data that is created by runaway processes. This approach is acceptable for scenarios with event-based undercover agents associated with tasks. The undercover agent makes up to five attempts, and after the last attempt, it stops, and nothing is required from the Process Portal user.
The cleanup utility, which is provided in the Admin Console, removes task data only, not all of the BPD instance data. The LSW_BPD_INSTANCE_DELETE stored procedure deletes both the instance and task data that is associated with the BPD. Therefore, it is a much more thorough way to clean out BPD instances. If you are using stand-alone services, consider running the cleanup utility after you run the LSW_BPD_INSTANCE_DELETE stored procedure. You might want your database administrator to construct a recurring job that queries for, then deletes, the instances that you must delete.
select inst.EXECUTION_STATUS, count(*) as total
from
LSW_BPD_INSTANCE inst,
LSW_TASK task
where task.BPD_INSTANCE_ID = inst.BPD_INSTANCE_ID
group by inst.EXECUTION_STATUS
select inst.EXECUTION_STATUS, count(*) as total
from
LSW_BPD_INSTANCE inst
group by inst.EXECUTION_STATUS