Brazos Portal EVENT_LOG table maintenance/cleanup

Follow

Introduction

In order for Brazos Portal to keep browser clients in sync it maintains an EVENT_LOG table. This table allows us to only send deltas when user reconnects, for instance at login or from loss of network connectivity. Brazos Portal takes care of the maintenance of this table, with one exception, DELETE events.

These DELETEs remain in the table to handle the case where a user isn't connected when the task gets removed. Next time they connect they will get that event, and know to update the browser cache removing the deleted task. This is the expected normal flow. Brazos Portal also compares checksums of the tasks on browser page load to make sure things are in sync. When the checksum is compared if there are extra tasks that should have been deleted but were not, the checksum will mismatch resulting in the browser re-downloading the entire task list. We can make use of this checksum activity to cleanup old DELETE events from the EVENT_LOG.

When and what to cleanup

Depending on your environment, you may need to do this more or less frequently. To some extent this should align with the frequency that you remove completed instances from your environment. For example, if you remove completed instance that are older than 30 days at the end of every month, then it would be good to remove DELETE events from the EVENT_LOG table a week or two later. If you cleanup instances nightly and the majority of your users are connected everyday, then a one day delay to cleanup DELETE events would keep the EVENT_LOG table as small as possible while not forcing complete task list reloads for the majority of users.

To get started we suggest that you look into your oldest entries in EVENT_LOG table and see if they are pretty dense with DELETE events or if the DELETE events are quite old. 

How to cleanup

In versions of Brazos Portal prior to v1.14.2, you should run the following SQL command prior to each execution of the SQL provided below that removes the DELETE events. This is related to DE1744 which is resolved in Brazos Portal v1.14.2.

--
-- Remove CREATE and UPDATE event log entries for tasks that have a 
-- DELETE event -- DELETE FROM brazos_portal.event_log WHERE type IN ('CREATE', 'UPDATE') AND task_id IN ( SELECT task_id FROM brazos_portal.event_log WHERE type = 'DELETE');

Once you have selected a timeframe, you can go ahead and remove all DELETE events. As an example here, we'll remove DELETE events older than seven days.

SQL queries examples

DB2:

1) Check the count of DELETE type of events older than 7 days:

select count(*) FROM brazos_portal.event_log where time < (CURRENT_DATE - 7 days) and type = 'DELETE'

2) Delete all DELETE type of events older than 7 days:

delete FROM brazos_portal.event_log where time < (CURRENT_DATE - 7 days) and type = 'DELETE' 

MSSQL: 

1) Check the count of DELETE type of events older than 7 days:

select count(*) FROM [brazos_portal].[event_log] where time < dateadd(day,-7,getdate()) and type='DELETE'

2) Delete all DELETE type of events older than 7 days: 

delete FROM [brazos_portal].[event_log] where time < dateadd(day,-7,getdate()) and type='DELETE'

Oracle:

1) Check the count of DELETE type of events older than 7 days:

select count(*) from brazos_portal.EVENT_LOG where TIME > SYSDATE - 7 and type='DELETE'

2) Delete all DELETE type of events older than 7 days:

delete from brazos_portal.EVENT_LOG where TIME > SYSDATE - 7 and type='DELETE'
Have more questions? Submit a request

Comments

Powered by Zendesk