We have over 300,000 records in our Process Log. We'd like to delete records that are not Running or Error that are over one month old. They are supposed to be archived, but there are records from months ago that have not been archived. When I select a record, there is no option to delete it or archive it. How do I delete or archive records from the Process Log?
Like
Dear Janine,
Process log gradually gets cleared by the archivation process. We don't recommend to delete data from Process Log yourself. However, if you really need to delete that data we recommend to send a request to the support@bpmonline.com if your instance is in cloud as it requires very resource heavy sql operations that would not be possible to track with sql executor.
If your instance is on-site you can take the instruction below as an example to adapt it based on your needs:
1) Register SQL procedures tsp_DeleteSysProcessLog.sql and tsp_DeleteSysProcessLogByStartDate.sql (procedures themselves below)
2) Execute the procedure.
Example: EXEC [dbo].[tsp_DeleteSysProcessLogByStartDate] 'Error', '2017-09-22', -1,
Where 'Error' is the status of processes, '2017-09-22' by which start date to delete processes, '-1' how many processes to delete, -1 means all processes.
Please note that the operation is very resource heavy and time consuming and we don't recommend doing that during business hours.
Best regards,
Dennis
IF NOT OBJECT_ID('[dbo].[tsp_DeleteSysProcessLogByStartDate]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_DeleteSysProcessLogByStartDate]
END
GO
CREATE PROCEDURE [dbo].[tsp_DeleteSysProcessLogByStartDate]
@Status nvarchar(max),
@StartDate date,
@RowsCountToDelete int
AS
BEGIN
SET NOCOUNT ON
IF NOT OBJECT_ID('#SysProcessLogId') IS NULL
BEGIN
DROP TABLE #SysProcessLogId
END
CREATE TABLE #SysProcessLogId (Id uniqueidentifier)
IF @RowsCountToDelete > -1
INSERT INTO #SysProcessLogId ([Id])
SELECT Top (@RowsCountToDelete)
SysProcessLog.Id
FROM SysProcessLog
JOIN SysProcessStatus ON SysProcessStatus.Id = SysProcessLog.StatusId
WHERE
SysProcessLog.ParentId IS NULL AND
SysProcessLog.StartDate < @StartDate AND
SysProcessStatus.[Name] = @Status
ELSE
INSERT INTO #SysProcessLogId ([Id])
SELECT
SysProcessLog.Id
FROM SysProcessLog
JOIN SysProcessStatus ON SysProcessStatus.Id = SysProcessLog.StatusId
WHERE
SysProcessLog.ParentId IS NULL AND
SysProcessLog.StartDate < @StartDate AND
SysProcessStatus.[Name] = @Status
EXEC [dbo].[tsp_DeleteSysProcessLog]
END
GO
CREATE OR REPLACE PROCEDURE "tsp_DeleteSysProcessLogById" (id VARCHAR2)
IS
TYPE IdSet IS TABLE OF VARCHAR2(38);
input_ids IdSet;
i NUMBER;
BEGIN
SELECT
"Id"
BULK COLLECT INTO input_ids
FROM "SysProcessLog"
WHERE "ParentId" = id;
IF input_ids.COUNT() > 0 THEN
FOR i IN input_ids.FIRST .. input_ids.LAST
LOOP
BEGIN
"tsp_DeleteSysProcessLogById"(input_ids(i));
END;
END LOOP;
END IF;
DELETE FROM "SysProcessElementLog"
WHERE "SysProcessId" = id;
DELETE FROM "SysProcessLog"
WHERE "Id" = id;
END;
/
CREATE OR REPLACE PROCEDURE "tsp_DeleteSysProcessLog" (inputRowsCountToDelete IN INT, status IN VARCHAR2)
IS
TYPE IdSet IS TABLE OF VARCHAR2(38);
input_ids IdSet;
i NUMBER;
step INT := 1000;
startDate TIMESTAMP;
rowsCountToDelete INT := inputRowsCountToDelete;
allRowsCount INT;
rowsLeft INT;
BEGIN
DBMS_OUTPUT.PUT_LINE('Database: ' || USER);
startDate := CURRENT_TIMESTAMP;
IF (inputRowsCountToDelete <= 0) THEN
SELECT COUNT("Id") INTO rowsCountToDelete FROM "SysProcessLog";
END IF;
SELECT
"SysProcessLog"."Id"
BULK COLLECT INTO input_ids
FROM "SysProcessLog"
WHERE
"SysProcessLog"."ParentId" IS NULL
AND "SysProcessLog"."StatusId" IN
(SELECT
"SysProcessStatus"."Id"
FROM "SysProcessStatus"
WHERE INSTR(status, "SysProcessStatus"."Value") > 0)
AND NOT EXISTS(SELECT "SysProcessData"."Id" FROM "SysProcessData" WHERE "SysProcessData"."Id" = "SysProcessLog"."Id")
AND ROWNUM <= rowsCountToDelete;
allRowsCount := input_ids.COUNT();
DBMS_OUTPUT.PUT_LINE('Number of records to process: ' || rowsCountToDelete);
IF allRowsCount > 0 THEN
rowsLeft := allRowsCount;
FOR i IN input_ids.FIRST .. input_ids.LAST
LOOP
BEGIN
IF MOD(i, step) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Step ready: ' || TO_CHAR(CURRENT_TIMESTAMP, 'DD-MM-YYYY HH24:MI:SS.FF2'));
DBMS_OUTPUT.PUT_LINE('Rows left: ' || rowsLeft);
END IF;
"tsp_DeleteSysProcessLogById"(input_ids(i));
rowsLeft := rowsLeft - 1;
END;
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Number of records processed: ' || allRowsCount);
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Execution time: ' || TO_CHAR((CURRENT_TIMESTAMP - startDate), 'DD-MM-YYYY HH24:MI:SS.FF2'));
END;
/
--CALL "tsp_DeleteSysProcessLog"(10000, '2,4');
Thanks, I have reported it to Support since there are records even from April that haven't been archived.
Van Ly,
Process log is connected to several other tables so there is a risk of not deleting all necessary records or possibly corrupting some necessary data.
Best regards,
Dennis