I am using Asemon v3.2.3 to monitor ASE instances in version 16.0 SP02 PL07 and 16.0 SP04 PL06 HF1. Both Asemon and ASE run on AIX 7.2
For all the instances some of the data for certain tables are kept way over the 'daysToKeep' parameter.
For example, for one asemon logger, the 'daysToKeep' is set to 31 days, the 'deleteSleep' is set to default 100, but for all of the tables, the retention period has been crossed by a week now (38 days), and for some by six months already (since enabling Asemon for this instance). See attached ss.
I check in that particular asemon's log that the Purge task seems to be working. The only error that is reported for this task is during ASE weekly restart on Sundays at 00:00 when the Purge task closes. Also, all asemon loggers are restarted also on Sundays at 23:00, so after the ASE restart.
Can you advise what could be the issue with this Purge task ?
these tables are never purged :
AseParams
SysMonFld2
TrendProc
Trends
TrendStmt
WClassInf
WEvInf
They are not big tables and keeping this info is interesting
Some tables keeps data if depending data still exists in other table (for exemple StmtPlan, StmObj, StmtPlnHK and StmtSQLHK depend on StmtStat)
If you suspect any problem with the purge process , you can start asemon_logger with debug flag 113
(asemon_logger .... -d 113)
and see all statements executed by purge thread
Regards
Jpm
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I see you had an error purging the OpObjAct. This purge is very basic : delete ?SERVERNAME?_OpObjAct where Timestamp < ?DATE?
may be this table is corrupted. Check it
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
thank you for a fast response. I will indeed restart that particular logger with the provided traceflag and post any findings worth sharing. will these extra statements be logged in the logger's log file or echoed to the terminal ? I will also try to delete rows from xxx_TrendsCfg table and restart asemon_logger to see if this helps.
as for the data dependency between mentioned tables , if StmtPlan, StmObj, StmtPlnHK and StmtSQLHK depend on StmtStat, then this does not make sense to me because if you look at the first ss, you'll see that first collected data from StmtStat is 38-day old. shouldn't the dependant tables' data also be this old, because StmtPlnHK and StmtSQLHK are 194-days old, so since the logger's deployment. or am I getting something wrong here?
as for the error on purging the OpObjAct - again, these errors show up during the monitored instance restart on Sunday at midnight. Then, on Sunday at 23:00, the logger gets restarted and up until next restart of the monitored there are no purge errors in the log. see 2nd ss, it shows the bottom of the log file which ends at Purge task start.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
so, I restarted the logger with flag 113 and the Purge task works indeed, but apparently it's very slow as it deletes 1000 rows at a time every 100 ms as per the attached ss with logs. It started with the biggest table for the monitored instance, XXX_OpObjAct. It's over 71 GB in size and has over 220 000 000 rows and there are over 40 000 000 rows with Timestamp < '2025/08/08 15:40:03' that are to be deleted as per daysToKeep=31
Is there a way to speed up this purging, e.g. deleting the rows in bigger than 1000 rows chunks ?
Hi elovelo,
you can change the parameter deleteSleep and set it to "1" rather than "100"
It 'll wait 1 ms between each delete packet of 1000 rows
Restart asemon_logger to make this change effective
Regards
Jpm
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello JPM,
I am using Asemon v3.2.3 to monitor ASE instances in version 16.0 SP02 PL07 and 16.0 SP04 PL06 HF1. Both Asemon and ASE run on AIX 7.2
For all the instances some of the data for certain tables are kept way over the 'daysToKeep' parameter.
For example, for one asemon logger, the 'daysToKeep' is set to 31 days, the 'deleteSleep' is set to default 100, but for all of the tables, the retention period has been crossed by a week now (38 days), and for some by six months already (since enabling Asemon for this instance). See attached ss.
I check in that particular asemon's log that the Purge task seems to be working. The only error that is reported for this task is during ASE weekly restart on Sundays at 00:00 when the Purge task closes. Also, all asemon loggers are restarted also on Sundays at 23:00, so after the ASE restart.
Can you advise what could be the issue with this Purge task ?
Last edit: elovelo 2025-09-05
Hi elovelo,
these tables are never purged :
AseParams
SysMonFld2
TrendProc
Trends
TrendStmt
WClassInf
WEvInf
They are not big tables and keeping this info is interesting
Some tables keeps data if depending data still exists in other table (for exemple StmtPlan, StmObj, StmtPlnHK and StmtSQLHK depend on StmtStat)
If you suspect any problem with the purge process , you can start asemon_logger with debug flag 113
(asemon_logger .... -d 113)
and see all statements executed by purge thread
Regards
Jpm
I didn't see you get error in the purge thread
Do a delete of all rows in xxx_TrendsCfg table and restart asemon_logger
Tell me if you still have error during next purge
I see you had an error purging the OpObjAct. This purge is very basic :
delete ?SERVERNAME?_OpObjAct where Timestamp < ?DATE?may be this table is corrupted. Check it
Hello, JP,
thank you for a fast response. I will indeed restart that particular logger with the provided traceflag and post any findings worth sharing. will these extra statements be logged in the logger's log file or echoed to the terminal ? I will also try to delete rows from xxx_TrendsCfg table and restart asemon_logger to see if this helps.
as for the data dependency between mentioned tables , if StmtPlan, StmObj, StmtPlnHK and StmtSQLHK depend on StmtStat, then this does not make sense to me because if you look at the first ss, you'll see that first collected data from StmtStat is 38-day old. shouldn't the dependant tables' data also be this old, because StmtPlnHK and StmtSQLHK are 194-days old, so since the logger's deployment. or am I getting something wrong here?
as for the error on purging the OpObjAct - again, these errors show up during the monitored instance restart on Sunday at midnight. Then, on Sunday at 23:00, the logger gets restarted and up until next restart of the monitored there are no purge errors in the log. see 2nd ss, it shows the bottom of the log file which ends at Purge task start.
Hello JP,
so, I restarted the logger with flag 113 and the Purge task works indeed, but apparently it's very slow as it deletes 1000 rows at a time every 100 ms as per the attached ss with logs. It started with the biggest table for the monitored instance, XXX_OpObjAct. It's over 71 GB in size and has over 220 000 000 rows and there are over 40 000 000 rows with Timestamp < '2025/08/08 15:40:03' that are to be deleted as per daysToKeep=31
Is there a way to speed up this purging, e.g. deleting the rows in bigger than 1000 rows chunks ?
Hi elovelo,
you can change the parameter deleteSleep and set it to "1" rather than "100"
It 'll wait 1 ms between each delete packet of 1000 rows
Restart asemon_logger to make this change effective
Regards
Jpm
Thank you. And I suppose there is no way to increase the packet size of these 1000 rows?
no, sorry, I didn't create a parameter for that
Anyway with deletesleep=1 the purge should be considerably faster
ok, JP. thank you, I will give ot a try with shorter deleteSleep