Here they are

-- pmse_email_message
DELETE em
FROM pmse_email_message em
INNER JOIN pmse_bpm_flow bf ON em.flow_id = bf.id
INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_email_message
DELETE em
FROM pmse_email_message em
INNER JOIN pmse_bpm_flow bf ON em.flow_id = bf.id
INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_email_message;

-- pmse_bpm_flow
DELETE bf
FROM pmse_bpm_flow bf
INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_bpm_flow
DELETE bf
FROM pmse_bpm_flow bf
INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_bpm_flow;

-- pmse_bpm_thread
DELETE bt
FROM pmse_bpm_thread bt
INNER JOIN pmse_inbox i ON bt.cas_id = i.cas_id
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_bpm_thread
DELETE bt
FROM pmse_bpm_thread bt
INNER JOIN pmse_inbox i ON bt.cas_id = i.cas_id
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_bpm_thread;

-- pmse_bpm_form_action
DELETE bfa
FROM pmse_bpm_form_action bfa
INNER JOIN pmse_inbox i ON bfa.cas_id = i.cas_id
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_bpm_form_action
DELETE bfa
FROM pmse_bpm_form_action bfa
INNER JOIN pmse_inbox i ON bfa.cas_id = i.cas_id
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_bpm_form_action;

-- pmse_inbox
DELETE i
FROM pmse_inbox i
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_inbox
DELETE i
FROM pmse_inbox i
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_inbox;

Hi John,

Thanks for asking this.

In Damien's post, the main challenge was not that the data can't be hard-deleted in the Data Archiver, but that there was not a dynamic filter, so a user would have to manually come in and change the before date in the filter and rerun it each time.

I recommend using Data Archiver for your stated purpose, as it is doable there.

Regards,
Patrick McQueen
Director, SugarCRM Support

Hello Patrick,

Can you ensure us that all the related tables records' would be deleted as well as the pmse_inbox table ?

We are facing an issue with one of our customer, pmse_inbox is 29Gb gross, pmse_bpm_flow 36 Gb...

I have no solution than deleting the data step by step.

Thanks for your feedback.

Best regards,

Enes

Hi Enes Saridogan,

My understanding of the Data Archiver functionality when 'pmse_Inbox' is the selected module is that it cleans up the following tables at a minimum:

pmse_inbox
pmse_bpm_flow
pmse_bpm_thread

There is an enhancement (90933) to change the label from 'pmse_Inbox' to a label more appropriate to indicate it has a wider application than the pmse_inbox table. 

Chris

Hi,

be carefull because with these queries all BPM that have an entry condition with "on first update" because it relies on "pmse_bpm_flow" content to know if the BPM has been trigger once or not.

Hi Enes Saridogan,

Data Archiver will delete the records on a scheduler, in batches, not all at once, so it will take time to carve through a large dataset. I recommend leaving the Data Archiver record active to let it run to catch up to deleting the filtered data.

The Data Archiver does not optimize the tables when deleting records, so if your ultimate goal is to reduce the data storage size estimated by MySQL, I recommend having someone optimize the tables once the Data Archiver has caught up to hard deleting the content you want gone.

If it did not take years to accumulate that volume of BPM data, I might also recommend having someone review the process designs for efficiency. I've seen many designs over the years where BPMs are designed to fire unnecessarily, causing avoidable inflation of these tables. Making the designs more efficient, or choosing other automation methods for some of the scenarios, can greatly reduce the storage that accumulates in these tables.

If the instance is hosted by SugarCRM and you'd like me to take a look, I encourage you to file a support case and provide me the case number so I can take ownership of it. This will enable me to look at the situation thoroughly and offer specific advice.

Regards,
Patrick McQueen
Director, SugarCRM Support

Hi Alexandre Moulin,

This is good advice.

While I usually recommend not using the "on first update" precisely for this reason, here are alternative queries that account for that concern:

#1 - Creates Temp table used to store the pmse_bpm_flow rows we want to keep.
CREATE TABLE pmse_bpm_flow_cleaned LIKE pmse_bpm_flow; INSERT INTO pmse_bpm_flow_cleaned SELECT * FROM pmse_bpm_flow WHERE pmse_bpm_flow.cas_id NOT IN (SELECT cas_id from pmse_inbox WHERE cas_status in ('COMPLETED', 'TERMINATED') AND date_modified < DATE_SUB(NOW(), INTERVAL 30 DAY) ) OR pmse_bpm_flow.cas_index = 1; 
​
#2 Swap pmse_bpm_flows_cleaned and pmse_bpm_flow tables
RENAME TABLE pmse_bpm_flow TO pmse_bpm_flow_backup, pmse_bpm_flow_cleaned TO pmse_bpm_flow;
​
#3 Creates temp table used to store the pmse_inbox rows we want to keep.
CREATE TABLE pmse_inbox_cleaned LIKE pmse_inbox; INSERT INTO pmse_inbox_cleaned SELECT * FROM pmse_inbox WHERE pmse_inbox.cas_status NOT IN ('COMPLETED', 'TERMINATED') OR pmse_inbox.date_modified > DATE_SUB(NOW(), INTERVAL 30 DAY) OR pmse_inbox.cas_id NOT IN (SELECT cas_id FROM pmse_bpm_flow WHERE cas_index = '1'); 
​
#4 Swap pmse_inbox_cleaned and pmse_inbox tables.
RENAME TABLE pmse_inbox TO pmse_inbox_backup, pmse_inbox_cleaned TO pmse_inbox;
​
#5 Verify everything looks good after the pruning of Completed/Terminated flows older than 30 days, then remove the backup tables.
DROP TABLE IF EXISTS pmse_bpm_flow_backup, pmse_inbox_backup;

These queries leave the first entry for each run in the pmse_bpm_flow table. For many, this can still prove to be a massive collection remaining in the table, but it accounts for the first update only start criteria where needed.

Regards,
Patrick McQueen
Director, SugarCRM Support

-- pmse_bpm_flow
DELETE bf
FROM pmse_bpm_flow bf
INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS'

AND bf.id not in* <put in exclusive sql by condition here>