{"id":7868,"date":"2015-11-09T17:51:36","date_gmt":"2015-11-10T00:51:36","guid":{"rendered":"https:\/\/ephesoft.com\/docs\/?p=7868"},"modified":"2020-08-27T16:27:52","modified_gmt":"2020-08-27T23:27:52","slug":"kb0010063-manually-cleaning-the-activiti-table","status":"publish","type":"post","link":"https:\/\/ephesoft.com\/docs\/kb0010063-manually-cleaning-the-activiti-table\/","title":{"rendered":"KB0010063: Manually Cleaning the Activiti Tables"},"content":{"rendered":"

Applies to: <\/strong>Ephesoft Transact 4.5.0.0 and above<\/span><\/p>\n

<\/a>Issue<\/span><\/h2>\n

A significant amount of workflow data can accumulate over time, which may hamper the overall application performance.<\/span><\/p>\n

<\/a>Solution<\/span><\/h2>\n

You can avoid this issue by manually cleaning up the Activiti-related tables using the instructions in this article, but this manual cleanup method is only recommended for extenuating circumstances.<\/span><\/p>\n

Important: <\/strong>Manually cleaning the Activiti tables will impact the accuracy of reporting data. Ephesoft does not recommend manual Activiti table cleanup. However, if there is a case of Activiti corruption, and it is important to get a system back online, this method may be the only alternative.<\/span><\/p>\n

<\/a>Stored Procedures<\/span><\/h3>\n
    \n
  1. Back up the Ephesoft database.<\/span><\/li>\n
  2. If the Advanced Reporting license is valid for the Ephesoft server, add @advanced<\/strong> to the IN clause in the job filter below:<\/span><\/li>\n<\/ol>\n
    SET @minimumDateTime = (SELECT MIN(last_execution_at) FROM <reportDB><\/em><\/strong>.dbo.last_execution where job IN(@batchInstance,@dashBoard,@standard));<\/span><\/pre>\n
    Note: <\/strong>Replace <reportDB><\/em><\/strong> with the actual report database name.<\/span><\/p>\n
      \n
    1. Create the procedure in the Ephesoft database.<\/span><\/li>\n
    2. In a separate SQL Query editor, run the following query to execute the procedure:<\/span><\/li>\n<\/ol>\n
      EXEC cleanActivity<\/span><\/pre>\n
      Note: <\/strong>This procedure applies to MSSQL only:<\/span><\/p>\n
      CREATE PROCEDURE cleanActivity\r\nAS\r\nBEGIN\r\nDECLARE @minimumDateTime AS DATETIME;\r\nDECLARE @delState AS VARCHAR(20);\r\nDECLARE @finishState AS VARCHAR(20);\r\nDECLARE @batchInstance AS VARCHAR(30);\r\nDECLARE @dashBoard AS VARCHAR(20);\r\nDECLARE @standard AS VARCHAR(20);\r\nDECLARE @advanced AS VARCHAR(20);\r\nSET @delState = 'DELETED';\r\nSET @finishState = 'FINISHED';\r\nSET @batchInstance = 'BATCH_INSTANCE_LAST_UPDATE';\r\nSET @dashBoard = 'DASHBOARD';\r\nSET @standard = 'STANDARD';\r\nSET @advanced = 'ADVANCED';\r\nSET @minimumDateTime = (SELECT MIN(last_execution_at) FROM report.dbo.last_execution where job IN(@batchInstance,@dashBoard,@standard));\r\nDELETE FROM ACT_HI_VARINST WHERE PROC_INST_ID_ IN (\r\nSELECT DISTINCT(proc_inst.PROC_INST_ID_) FROM ACT_HI_PROCINST proc_inst JOIN batch_instance batch_inst ON proc_inst.NAME_=batch_inst.identifier\r\nWHERE batch_inst.batch_status IN (@delState,@finishState) AND batch_inst.last_modified <@minimumDateTime);\r\nDELETE FROM ACT_HI_ACTINST WHERE PROC_INST_ID_ IN (\r\nSELECT DISTINCT(proc_inst.PROC_INST_ID_) FROM ACT_HI_PROCINST proc_inst JOIN batch_instance batch_inst ON proc_inst.NAME_=batch_inst.identifier\r\nWHERE batch_inst.batch_status IN (@delState,@finishState) AND batch_inst.last_modified <@minimumDateTime);\r\nDELETE FROM ACT_HI_PROCINST WHERE PROC_INST_ID_ IN (\r\nSELECT DISTINCT(proc_inst.PROC_INST_ID_) FROM ACT_HI_PROCINST proc_inst JOIN batch_instance batch_inst ON proc_inst.NAME_=batch_inst.identifier\r\nWHERE batch_inst.batch_status IN (@delState,@finishState) AND batch_inst.last_modified < @minimumDateTime);\r\nEND<\/span><\/pre>\n
        \n
      1. After the procedure executes, all the Activiti-related data for batches in DELETED and FINISHED state will be deleted from the following tables:<\/span>\n