I have a historian that has been collecting since 2015 with data pruning disabled. I recently enabled the data pruning at 2 years as shown in the screen shot. The data is partitioned into one month increments. So far no data has been removed.
Is there a step I missed? If there isn’t, when does the prune feature remove data?
The tags.history.query.PartitionManager logger on DEBUG/TRACE may have some information; the prune task is supposed to execute every 10 minutes.
INFO | jvm 1 | 2020/12/16 14:36:58 | D [t.h.q.PartitionManager ] [22:36:58]: Ignition_History (id=8ec1d362) Partition query returning result set: [sqlt_data_3_2020_12 [Tue Dec 01 00:00:00 PST 2020-Fri Jan 01 00:00:00 PST 2021 @ 0]] Query: 'SELECT "pname", "start_time", "end_time", "blocksize", "flags" FROM sqlth_partitions WHERE "drvid"=? and "start_time"<=? and "end_time">=? and ("blocksize" is NULL or "blocksize"<=?) ORDER BY "start_time" ASC' Params: [[3, 1607862600000, 1607257800000, 604800000]] queryid=8ec1d362
INFO | jvm 1 | 2020/12/16 14:36:58 | D [t.h.q.PartitionManager ] [22:36:58]: Ignition_History (id=8ec1d362) Partition query returning result set: [sqlt_data_3_2020_12 [Tue Dec 01 00:00:00 PST 2020-Fri Jan 01 00:00:00 PST 2021 @ 0], sqlt_data_3_2020_11 [Sun Nov 01 00:00:00 PDT 2020-Tue Dec 01 00:00:00 PST 2020 @ 0]] Query: 'SELECT TOP 2 "pname", "start_time", "end_time","blocksize", "flags" FROM sqlth_partitions WHERE "drvid"=? and "start_time"<? and ("blocksize" is NULL or "blocksize"<=?) ORDER BY "end_time" DESC' Params: [[3, 1607257800000, 0]] queryid=8ec1d362
INFO | jvm 1 | 2020/12/16 14:36:58 | D [t.h.q.PartitionManager ] [22:36:58]: Ignition_History (id=8ec1d362) Partition query returning result set: [sqlt_data_3_2020_12 [Tue Dec 01 00:00:00 PST 2020-Fri Jan 01 00:00:00 PST 2021 @ 0]] Query: 'SELECT "pname", "start_time", "end_time", "blocksize", "flags" FROM sqlth_partitions WHERE "drvid"=? and "start_time"<=? and "end_time">=? and ("blocksize" is NULL or "blocksize"<=?) ORDER BY "start_time" ASC' Params: [[3, 1607862600000, 1607862600000, 0]] queryid=8ec1d362
INFO | jvm 1 | 2020/12/16 14:38:08 | D [t.h.q.PartitionManager ] [22:38:08]: Ignition_History Partition query returning result set: [] Query: 'SELECT "pname", "start_time", "end_time","blocksize", "flags" FROM sqlth_partitions WHERE "drvid"=? and "end_time"<=? ORDER BY "start_time" ASC' Params: [[3, 1545086288425]]
INFO | jvm 1 | 2020/12/16 14:38:08 | D [t.h.q.PartitionManager ] [22:38:08]: Ignition_History Tag History maintenance task ran and did not delete anything.
INFO | jvm 1 | 2020/12/16 14:48:08 | D [t.h.q.PartitionManager ] [22:48:08]: Ignition_History Partition query returning result set: [] Query: 'SELECT "pname", "start_time", "end_time","blocksize", "flags" FROM sqlth_partitions WHERE "drvid"=? and "end_time"<=? ORDER BY "start_time" ASC' Params: [[3, 1545086888415]]
INFO | jvm 1 | 2020/12/16 14:48:08 | D [t.h.q.PartitionManager ] [22:48:08]: Ignition_History Tag History maintenance task ran and did not delete anything.
INFO | jvm 1 | 2020/12/16 14:51:58 | D [t.h.q.PartitionManager ] [22:51:58]: Ignition_History (id=8ec1d362) Partition query returning result set: [sqlt_data_3_2020_12 [Tue Dec 01 00:00:00 PST 2020-Fri Jan 01 00:00:00 PST 2021 @ 0]] Query: 'SELECT "pname", "start_time", "end_time", "blocksize", "flags" FROM sqlth_partitions WHERE "drvid"=? and "start_time"<=? and "end_time">=? and ("blocksize" is NULL or "blocksize"<=?) ORDER BY "start_time" ASC' Params: [[3, 1607862600000, 1607257800000, 604800000]] queryid=8ec1d362
INFO | jvm 1 | 2020/12/16 14:51:58 | D [t.h.q.PartitionManager ] [22:51:58]: Ignition_History (id=8ec1d362) Partition query returning result set: [sqlt_data_3_2020_12 [Tue Dec 01 00:00:00 PST 2020-Fri Jan 01 00:00:00 PST 2021 @ 0], sqlt_data_3_2020_11 [Sun Nov 01 00:00:00 PDT 2020-Tue Dec 01 00:00:00 PST 2020 @ 0]] Query: 'SELECT TOP 2 "pname", "start_time", "end_time","blocksize", "flags" FROM sqlth_partitions WHERE "drvid"=? and "start_time"<? and ("blocksize" is NULL or "blocksize"<=?) ORDER BY "end_time" DESC' Params: [[3, 1607257800000, 0]] queryid=8ec1d362
INFO | jvm 1 | 2020/12/16 14:51:58 | D [t.h.q.PartitionManager ] [22:51:58]: Ignition_History (id=8ec1d362) Partition query returning result set: [sqlt_data_3_2020_12 [Tue Dec 01 00:00:00 PST 2020-Fri Jan 01 00:00:00 PST 2021 @ 0]] Query: 'SELECT "pname", "start_time", "end_time", "blocksize", "flags" FROM sqlth_partitions WHERE "drvid"=? and "start_time"<=? and "end_time">=? and ("blocksize" is NULL or "blocksize"<=?) ORDER BY "start_time" ASC' Params: [[3, 1607862600000, 1607862600000, 0]] queryid=8ec1d362
That query is asking for drvid=3, but your screenshot is a bunch of tables from drvid=1 (the first digit in the sqlt_data_X table names). Look at what’s in the sqlth_drv table - either you’ve changed gateway names, and the old gateway name is what all the drvid=1 entries were logged under, or there’s another gateway running somewhere else that’s storing to the same DB as drvid=1.
Starting in Feb. 2020 the table names are dbo.sqlt_data_3_2020_02. I know we have changed this gateway’s name. It may have been around then. Is there a nice way to get the tables dropped or do I need to do it by hand?
This is sqlth_drv. There are no gateways currently running called ignitionproduction.
