Hi All,

I'm trying to solve a very urgent problem about bpt tables. I need to reset all bpt tables from my environment because we have an application that developed using a wrong way with bpt to maintaing active process without terminating and generating a lot of data on all bpt tables. for example the OSSYS_BPM_EVENT_QUEUE table have 1.803.532.503 lines. :(

Anyone have an script or another solution to reset or reinstall only bpt database or another idea?

I'm tryied to use BPT_API and timers to solve this but the application create more new process quickly than BPT_API can clean.

Hi,

You can create a timer & then in the timer action .. loop through all the Process Entities & in iteration of each call ProcessTerminate action. It requure ProcessID.

It will set all the processs as InActive.


Hi Igor,


If you need to clean your database, especially in such extreme situation, just terminating the process is not enough. You'll need to actually delete those records.


Before anything, it is important to fix the application that is generating this huge volume of processes.


Secondly, there's a native extension called BPT_API which comes with a Process_BulkDelete action that should be used.


However, since you seem to have many million Process records that need to be deleted, using this API might not suffice and result in timeouts -- even when running this with a smaller 'records to delete per-bulk' number. If that's the case: you might need to use the help from a DBA and come up with a SQL script that deletes those records directly from the OSSYS_PROCESS table (records from peripheral tables are deleted in cascade). Once the mess is cleared, then you can create a timer that uses the BPT_API for deleting closed/unused processes every week, keeping the database clean.

Hi Caio,

Thanks for the sharing. I would like to explore on same. Because even I have records on bpt logs which generated while dev.

Thanks Caio,

I trying to delete from bpm_process table directly but as I sad the application is more faster to create registers than I can delete this registers.

I thinking the better way is stop all bpt applications, terminating all processes and do a delete but I will generate a outage on the environment.

My post intention was to share this problem and get feedback if anyone get the same problem to solve. My database is very full of data from bpt and will crash soon.

thanks again.

Hi Igor,


Sounds like immediate action must be taken before your DB completely fills up. You might want to open a high priority support ticket to get some help from the OutSystems staff.


In the meantime, I would try the following steps, in order:

  1. Lock the inflated process through Service Center > Monitoring > Processes
  2. Fix the application that is misbehaving
    • Review the logic that is launching new processes rapidly and disable it
    • In Service Studio, consider deleting the Process Definition altogether and create a brand new one if needed
  3. Create a timer that implements Process_BulkDelete from BPT_API, schedueld to run on a weekly basis at least


There is a supported tool called BPT Utils that aids this type of situation, but in extreme cases it is not practical and a more automated intervention on the data model might be needed. However, this is not officially supported nor endorsed by OutSystems.


If you find yourself in the latter extreme case, try deleting the unwanted data directly from the OSSYS_BPM_PROCESS table:

  • Work your WHERE condition to avoid deleting legitimate processes
  • Every bit of performance helps, so try to make the most optimal query possible: avoid including JOINs in your query and try to figure out the Process Definition ID beforehand in order to filter it directly by ProcessDefID in the WHERE condition of your DELETE command
  • Include a TOP (500) command (or lower) so your query does not attempt to delete millions of records in a single run, run this command repeatedly (and commit between each run) until all unwanted records are cleared
  • This will probably take a very long time (several hours depending on the volume to be deleted)


I hope this helps,

Thanks!

Solution

Hi All!

I'm solved my problem studing the Outsystems Database Model and create an script to clean bpt_process, bpt_activity and bpt_queue tables and restarting these tables sequences. Now my Database is clean and faster.

Thanks for all help.

Solution