I would try to do it in steps – do a retention time of 200, then 150, 100, etc or something like that.
If you want to see if the cleanup is still running you can look in the ‘delete_tasks’ table and look for all the ‘ended_at = null’ order by ‘updated_at’
That's what I'm trying now, since duration does not seem to work.
Thanks for the 'delete_tasks' tip - I did not know, I used to count (*) on JobRunEvent table to get this info.
I do see a bunch of recent JobRunEvent related entries in delete_tasks with ended_at = null (these are all attempts hard terminated in NSH)
do you think it represents a problem?
If the updated_at time is not w/in the last few hours it’s pretty likely that it’s not running anymore. You could have the dba look for any running update or delete statements running in the db for any of the job_run_event related tables (deploy_job_run_event, etc) to be sure.
Those should not be an issue though.
so I guess I'll have to use the retention time as a limiting factor to split this operation into multiple phases (and prior to that would have to determine from SQL how many rows will be affected and will figure out how long it will take)
Which is fine but any idea why 'duration' does not work at all? At some point I thought maybe it was minutes not seconds so I set it to 5 - no luck.
I've been testing cleanupHistoricalData JobRunEvent with different parameters and determined there are several problems with it (problems are the same under both 8.0 SP5 and 8.2 SP1)
1) duration parameter is ignored.
2) somethimes command to delete millions of row is executed and within a minute or two says "stored procedure completed successfully" but "delete_tasks" table shows the process active and deleting rows for hours and hours after that.
3)I get lots of db connection resets which stop massive cleanups (could be my environment)
4) it is unclear how to not specify a role as a third parameter to cleanupHistoricalData (do I put empty "" - I don't want to limit to any role)
1) I noticed 'RESULTS_RETENTION_TIME' is mentioned inseveral posts where cleanupHistoricalData JobRunEvent is also mentioned. Does it matter if I have it set to value 90 when I run for example 'Delete cleanupHistoricalData JobRunEvent 180 600 BLAdmins' It is my understanding that '90' is irrelevant in this case and is only used when retention policy is executed?
2)to estimate how many rows will be deleted by my next cleanupHistoricalData JobRunEvent command i have to assume that If job_run_event.event_date + retention days parameter is used and then I guess for 180 days SQL should be something like:
"SELECT count(*) as RowsToDelete from job_run_event where (DATEDIFF(day,event_date+180,GETDATE())>=0"?
1 of 1 people found this helpful
2 and 3 might be related ( the quick finish and db issues). for 4 use 'null' so like:
blcli Delete cleanupHistoricalData JobRunEvent 60 null null
i use null for the duration because i want to run it to completetion. so it sounds like you found an issue there and i'd open a ticket for that.
the result_retention_time determines how long the job run will show in the gui. the jobrunevent 'retentionTime' will determine how long the job run log messages will be kept. so it's possible for a job run to exist in the gui and not have any log messages inside it because the result_retention_time is 180, and you ran cleanupHistorical JobRunEvent w/ a retention time of 60.
all that matters for estimating how many rows will be deleted is if the event_date is < the retention period you pass it.
the result_retention_time only affects the job run and that's handled by the executeRetentionPolicy