Delete records older than xxx in historica transaction group

Ignition
1

Our database is getting quite large (185GB +), so I want to try to reduce the size to keep things running smoothly. I talked with facilities/operations, and we agreed that we can get rid of transaction group data that is older than 2 years old.

I have enabled the “Delete records older than” on two of our historical transaction groups for records older than 2 years, but the number of rows have not reduced at all after 30 minutes. This group is set to execute every 1 minute, and we have data back from 2010 in these tables, so there’s definitely data older than 2 years. Does this “delete older than” function run every time the group executes, or is it something that runs once a day or similar?

The console output the first time after setting logging to “all is”:
(D) 10:17:28 AM V1E1_MCM_History Historical forward transaction completed successfully.
(D) 10:17:28 AM V1E1_MCM_History Group evaluation completed.
(D) 10:17:28 AM V1E1_MCM_History Executing post-trigger evaluation.
(D) 10:17:28 AM V1E1_MCM_History Trigger is active.
(D) 10:17:28 AM V1E1_MCM_History Executing pre-trigger evaluation.
(D) 10:17:28 AM V1E1_MCM_History Starting group evaluation.

Every minute after setting to all, there has been an entry like:
(D) 10:32:29 AM V1E1_MCM_History Historical forward transaction completed successfully.

I am running version 7.6.2-rc4 (b2365). Any suggestions would be appreciated on how to remedy this.

2

Nevermind, I Just came back from lunch, and it appears the delete action has finally been triggered.

I’m still curious as to when this executes though. The initial set of records being deleted after my enabling this will be a pretty huge deletion set (the first table i tried this on was reduced from 1.7 million rows to 1 million), but after it’s all setup it shouldn’t be too bad. However, we have 87 transaction groups running. If we get an average of 4 entries a minute, and the cleanup script runs every 2 hours for example, that’s still 47,000 records to be deleted every 2 hours spread out across multiple tables. Will this cause any slowdown issues?

Also, the table that I initially tried this on now has an overhead of 2.4GB (on a table initially 5.8GB). Is there a way to enable automatic table optimizations after this deletion script fires as well?

3

Records are deleted on a schedule based on what amount of time you are keeping.
ie: for Delete records older than X:
if x < 1 hour, deletes happen every minute
if 1 hour < x < 12 hours, every 15 min
if x > 12 hours, every hour

As for you database keeping a large table size after the delete, that is controlled by the database, and Ignition has no way to affect it. You can look into your database settings to change it.