Delete Workflow History for Inactive Records

Purpose: As years pass by, the database size increases and you would like to delete unwanted records especially workflow history. This document explains you how to delete workflow history.

To delete the workflow history, execute the below scripts:

delete
from wftransaction
where wfid not in
(
  select wfid
  from wfinstance
  where active = 1
)
  and transdate <= (current timestamp - 1 month)
  and memo is null;
commit;

delete
from wfcallstack
where wfid in
(
  select wt.wfid
  from wftransaction wt
    ,wfinstance wi
  where wt.wfid = wi.wfid
    and wi.active = 0
    and transdate <= (current timestamp - 1 month)
    and memo is null
);
commit;

delete
from wfassignment
where wfid in
(
  select wt.wfid
  from wftransaction wt
    ,wfinstance wi
  where wt.wfid = wi.wfid
    and wi.active = 0
    and transdate <= (current timestamp - 1 month)
    and memo is null
);
commit;

delete
from wfinstance
where wfid in
(
  select wfid
  from wftransaction
  where memo is null
    and transdate <= (current timestamp - 1 month)
)
  and active = 0;
commit;

delete
from logintracking
where attemptdate <= (current timestamp - 3 month);
commit;