Question

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

4 comments

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.

I found Beesender tried to delete process log by a process to delete items from Process log (actual). Is that correct or is there any risk of doing that way? 

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  

Log in or register to comment