7 Replies Latest reply on Jul 6, 2012 11:32 AM by afurman NameToUpdate

    Duration parameter for Delete : cleanupHistoricalData

      Bladelogic 8.0 SP5/SQL Server 2005:



      Trying to delete large volume of JobRunEvent (80 million rows) and want to control how long this operation will take

      Checked all published and unpublished documentation for Delete:cleanupHistoricalData and Duration parameter (seconds)  seems to be the way to do it.

      But it seems to have no effect on the execution of deleteHistricalData (I've tried all possible values including those from 'examples' in the documentation)

      The process does not stop once the duration limit specified has been reached and runs forever...




      "blcli_execute Delete cleanupHistoricalData JobRunEvent 90 600 BLAdmins"


      documentation only has examples but does not tell you if there's a minimum value for Duration parameter

      ad how it is used - my understanding is that the operation will stop after either all data satisfying retention time has (90 above) has been deleted or number of seconds (600 above will elapse)


      Description for duration parameter says: "maximum duration for the operation in seconds, if applicable"


      Am I doing something wrong here?

        • 1. Re: Duration parameter for Delete : cleanupHistoricalData
          Bill Robinson

          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’

          • 2. Duration parameter for Delete : cleanupHistoricalData

            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?

            • 3. Re: Duration parameter for Delete : cleanupHistoricalData
              Bill Robinson

              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.

              • 4. Re: Duration parameter for Delete : cleanupHistoricalData

                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.

                • 5. Re: Duration parameter for Delete : cleanupHistoricalData

                  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)


                  Also questions:


                  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"?

                  • 6. Re: Duration parameter for Delete : cleanupHistoricalData
                    Bill Robinson

                    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

                    1 of 1 people found this helpful