We are running Camunda 7.x with a PostgreSQL database that has grown to a few GBs. Now that TTL has been enabled for historical data, we would like to understand the recommended SQL-based approach to clean up existing data safely.
Can you please guide us on the correct SQL queries or cleanup procedure to remove outdated history records without impacting the engine?
Hi there. I did a cleanup recently on SQL Server. You can use the nuclear option which is to drop the tables, they will be recreated on startup and everything will be gone but fresh.
Then you can truncate the tables. Safer but also wipes everything. Here is a script (use at your own risk, I’ve used it in dev but for prod test it first thoroughly)
– Truncate in correct order (children first)
TRUNCATE TABLE ACT_HI_DEC_IN;
TRUNCATE TABLE ACT_HI_DEC_OUT;
TRUNCATE TABLE ACT_HI_DECINST;
TRUNCATE TABLE ACT_HI_DETAIL;
TRUNCATE TABLE ACT_HI_VARINST;
TRUNCATE TABLE ACT_HI_ACTINST;
TRUNCATE TABLE ACT_HI_TASKINST;
TRUNCATE TABLE ACT_HI_IDENTITYLINK;
TRUNCATE TABLE ACT_HI_INCIDENT;
TRUNCATE TABLE ACT_HI_JOB_LOG;
TRUNCATE TABLE ACT_HI_EXT_TASK_LOG;
TRUNCATE TABLE ACT_HI_COMMENT;
TRUNCATE TABLE ACT_HI_ATTACHMENT;
TRUNCATE TABLE ACT_HI_OP_LOG;
TRUNCATE TABLE ACT_HI_BATCH;
TRUNCATE TABLE ACT_HI_CASEINST;
TRUNCATE TABLE ACT_HI_CASEACTINST;
TRUNCATE TABLE ACT_HI_PROCINST;
To delete 30 days history here is a script. Again I’ve used it in Dev with success but don’t just paste and go in prod. Test thoroughly first.
– =============================================– Camunda 7 History Purge - SQL Server– Deletes completed process instance history older than 30 days– =============================================
DECLARE @cutoffDatecutoffDate DATETIME = DATEADD(DAY, -30, GETDATE())@batchSizeDECLARE @batchSize I@rowsDeletedT = 1000;DECLARE @rowsDeleted INT = 1;
– Step 1: Create temp table of old completed process instance IDsSELECT PROC_INST_ID_INTO #oldInstancesFROM ACT_HI_PROCINSTWHERE END_TIME_ IS NOT NULL –@cutoffDateND_TIMEonly@cutoffDatecompleted processesAND @cutoffDateND_TIME_ < @cutoffDat@ROWCOUNT;
PRINT 'Process instances to purge@ROWCOUNT ’ + CAST(@@ROWCOUNT AS VARCHAR);
– Step 2: Delete from child tables in batches– E@rowsDeletedetailsch bl@rowsDeletedck loops until all matc@rowsDeleteding rows ar@batchSizeELETE gone
@batchSize– 2a. Variable @rowsDeletedetailsSET @rowsDeleted@rowsDeleted= 1;WHILE @rowsDelet@ROWCOUNTldInstancesd > 0B@rowsDeletedGIN@ROWCOUNTbatchSizeELETE TOP (@batchSize) FROM ACT_HI_DETAILW@rowsDeletedERE PROC_INST_ID_ IN (SE@rowsDeletedVariableECT PROC_INST_ID_ F@rowsDe@rowsDeletedetedOM #@ROW@batchSizeOUNTldInstances);SET @@batchSizeowsDeleted = @@ROWCOUNT;PRINT 'ACT_HI_D@rowsDeletedTAIL deleted: ’ + CAST(@rowsDeleted AS VARCHAR)@rowsDeletedEND@ROWCOUNT
– 2b.@rowsDeletedVariable ins@rowsDeletedancesSET @@rowsDeletedowsDeleted =@rowsDeleted1;WHILE @ba@rowsDeletedctivitychSize > 0BEGI@rowsDeletedDELETE TOP (@batchSi@rowsDeletede) FROM ACT_HI_VARINST@batchSizeHERE PROC_INST_ID_ IN (SELECT PRO@rowsDeleted_INS@ROWCOUNT_ID_ FROM #oldInstances);SET @rowsDeleted = @@ROWCOU@rowsDeletedT;P@ROWCOUNTIN@rowsDeleted 'ACT_HI_VARINST deleted: ’ + CAST(@row@rowsDeletedDeleted AS VARCHAR)@rowsDeletedEND
– 2c. @rowsDeletedctivity i@rowsDeletedstancesSET @rowsDeletedrowsDeleted@batchSize= @batchSize;WHILE @rowsDeleted > 0BEGINDELETE TOP (@batchSize) FROM ACT_HI_ACTINSTWHERE PROC_INST_ID_ IN@rowsDeleted(SEL@R@rowsDeletedWCOU@ROWCOUNTTCT PROC_INST_ID_ FROM #oldInstances);SET @rowsDeleted@rowsDeleted= @@ROWCOUNT;PRINT 'ACT_HI_ACTINST deleted: ’ + CA@rowsDeletedT(@rowsDeleted AS VARCH@rowsDeletedR);@rowsDeletedEND
– 2d. T@rowsDeletedsk ins@batchSizeancesS@batchSizeT @rowsDeleted = 1;WHILE @rowsDeleted > 0BEGINDELETE TOP (@batchSize) FROM ACT_HI_TASKINSTWHERE PR@rowsDeletedldInstancesC@rowsD@rowsDeletedlete@ROWCOUNTINST@ROWCOUNTID_ IN (SELECT PROC_INST_ID_ FROM #oldInsta@rowsDeletedces);SET@rowsDeleted@row@batchSizeARCHARDeleted = @@ROWCOUNT;PRINT @rowsDeletedACT_HI_TASKI@rowsDeletedST deleted: ’ + CAST(@r@batchSizewsDeleted AS VARCHA@rowsD@ROWCOUNTNCIDENTleted);END
– 2e@rowsDeleted Identity links (uses R@batchSizeOT_PROC_@rowsDeletedNST_ID_)SET @r@ROWCOUNTwsDeleted = 1;WHILE @rowsDeleted > 0BEGIND@batchSizeLETE TOP@rowsDeleted(@batchSize) FROM ACT_HI_IDENTITYLINK@rowsDeletedWHE@rowsDele@rowsDeletededE RO@ROWCOUNTT_PROC_I@batchSizeST_ID_ IN (SELECT PROC_INST_ID_ FROM #@rowsDeletedldInstances);SET @rowsDeleted = @@ROWCOUNT;PRINT 'ACT_HI@rowsDele@rowsDeletededID@ROWCOUNTNTITYLINK@rowsDeleteddeleted: ’ + CAST(@rowsDeleted AS @batchS@rowsDeletedzeARCHAR);END
– 2f. IncidentsSET @rowsDelete@rowsDeleted = 1;WHILE @rowsDeletedrowsDeleted > 0BEGINDELETE @ROWCOUNTOPOP@batchSize(@batchSize) FROM AC@rowsDeleted_HI_@ROWCOUNTNCIDENTWHERE PROC_INST_ID_ IN (SELECT PROC@rowsDeletedINST_ID_ FROM #oldInstances);SET @rows@rowsDeletedelet@ROWCOUNTd =@rowsDeleted@@ROWCOUNT;PRINT 'A@rowsDeletedT_HI_INCI@rowsDeletedENT deleted: @batchSize + CAST(@rowsDe@rowsDeletedeted AS VARCHAR);END
@rowsDeleted @ROWCOUNTg. Job logSET @rowsD@batchSizeleted = 1;WHILE @rowsDeleted > 0BEGINDELETE TOP (@batchSize) F@rowsDeletedOM A@ROWCOUNTT_HI_JOB_LOGWHERE PROCESS_INSTAN@rowsDeletedE_ID@ROWCOUNT @batchSizePRINTN@rowsDeleted(SELECT PROC_INST_ID_ @rowsDeletedROM #ol);SET @rowsDeleted = @@ROWCOUNT;PR@rowsDeletedNT@rowsDeleted’A@ROWCOUNTWHILET@rowsDeletedHI_JOB_LOG deleted@batc@batchSizeSize ’ + CAST(@rowsDeletedTTACHMENTrowsDeleted AS VARCHAR);END
– 2h. External task logSET @rowsDeleted = 1;WHILE@batchSize@row@ROWCOUNTDeleted > 0BEGINDE@rowsDeletedETE @ROWCOUNT@rowsDeletedP (@batchSize) FROM ACT_HI_EXT_TASK_LOGWHERE PROC_INST@rowsDe@rowsDeletedetedID_ IN (@rowsDeletedELECT PROC_INST_ID@rowsDeletedOP F@batchSizeOM #old@rowsDeletednstances);S@rowsDeletedT @rowsDeleted = @@ROWCOUNT;PRINT 'ACT_HI@batchSizeEXT_TASK_LOG dele@rowsDeleteded: @ROWCOUNT + CAST(@rowsDel@batchSizeP_LOGted AS VARCHAR);EN@rowsDeleted
– 2i. CommentsSET @rowsDeleted = 1;WHI@rowsDeletedE @rowsDeletednstarowsDeleted @ROWCOUNT 0BEGI@rowsDeletedDELETE TOP @rowsDeleted@batchSize) FROM ACT_HI_COMMEN@row@batchSizeDeletedWHERE PROC_INST_ID_ IN (SELECT PROC_INST@rowsDeletedID_ FROM #oldI@rowsDelete@rowsDeletedInst@ROWCOUNTnces);SET @rowsDeleted = @@ROWCOUNT;@batch@rowsDeletedizePRINT 'ACT_HI_COMMENT deleted: ’ + CAST(@rowsDeleted AS VARCHAR);END
– 2j. AttachmentsSET @rowsDeleted@rowsDeleted= 1;@ROWCOUNTWHILE @rowsDeleted > 0BEGINDELETE TOP (@batchSize) FROM ACT_HI_@rowsDeletedTTACHMENTWHERE PROC_INST_ID_ IN (SELECT PR@rowsDeletedC_INST_ID_ F@rowsDeletedOM #oldInstances);SET @batchSize = @@ROWCOUNT;PRINT 'ACT_HI_ATTACHMENT deleted: ’ + CAST(@rowsDeleted AS VARCHAR);END
– 2k. Ope@rowsDeletedatio@ROWCOUNT logSET @rowsDeleted = 1;WHILE @rowsDeleted > 0BEGINDELETE @rowsDeletedOP (@batchSize) FROM ACT_HI_OP_LOGWHERE PROC_INST_ID_ IN (SELECT PROC_INST_ID_ FROM #oldI@rowsDeletedstances);SE@rowsDeleted @rowsDeleted = @@ROWCOUNT;PRINT 'ACT_HI_@batchSizeP_LOG deleted: ’ + CAST(@rowsDeleted AS VARCHAR);END
– Step 3: Finally delete the process @rowsDeletednsta@ROWCOUNTces themselvesSET @rowsDeleted = 1;WHILE @rowsDeleted > 0BEGIN@rowsDeletedDELETE TOP (@batchSize) FROM ACT_HI_PROCINSTWHERE PROC_INST_ID_ IN (SELECT PROC_INST_ID_ FROM #oldInstances);SET @rowsDeleted = @@ROWCOUNT;PRINT 'ACT_HI_PROCINST deleted: ’ + CAST(@rowsDeleted AS VARCHAR);END
– CleanupDROP TABLE #oldInstances;
PRINT ‘Purge complete!’;