12 Replies Latest reply on Oct 21, 2015 1:37 PM by Bill Robinson


    Edgar Lima

      Good afternoon.
      I have a client who does not perform the cleanup of the database up to 6 years.
      I need ideas on how to streamline the process. And if anyone knows an average of how long it will take , customer database has 160 GB .

      I believe I have two outputs :

      1 - Run the oficial job BMC with a lower period of retention .

      2 - Run Manual way Cleaning Jobs.

      Either way , the client will shut down application server and i must give an estimate of how long it will clear the 160 GB of logic blade database .


      Best Regards

      Edgar Caldeira

        • 1. Re: BSA DB CLEANUP HELP !
          Bill Robinson

          1 – open a ticket w/ support and post the #

          2 – what db is it ?  there are some scripts we should run to look at table sizes and see what cleanups to start running.

          3 – what version of bsa ?


          Btw, 160gb is not very large…

          • 2. Re: BSA DB CLEANUP HELP !
            Edgar Lima

            Hello Bill.

            It's a Oracle DB.

            Attached is the BLADELOGIC_DBSIZE.

            The version of bladelogic is 8.5

            • 3. Re: BSA DB CLEANUP HELP !
              Bill Robinson

              8.5.00 or 8.5.01 ?


              so the rowcounts didn't come through.  by size these are the largest tables:

              SNAPSHOT_BLFILE            TABLE                     8478
              BLFILE                     TABLE                     6242
              AUDIT_BLFILE               TABLE                     3811
              SNAPSHOT_COM_OBJECT        TABLE                     3701
              JOB_RUN_EVENT              TABLE                     3008
              AUDIT_COM_OBJECT           TABLE                     2100
              AUDIT_TRAIL                TABLE                      914
              DEPLOY_JOB_RUN_EVENT       TABLE                      658
              BLFILE_CDC                 TABLE                      543
              COM_OBJECT                 TABLE                      264
              PROP_SET_INSTANCE_PROP_VAL TABLE                      154
              JOB_RESULT_DEVICE          TABLE                      128
              AUDIT_COM_OBJECT_CNT       TABLE                      126
              AUDIT_BLFILE_CNT           TABLE                      100



              do you know what retention policy the customer has for their job result data ? (RESULTS_RETENTION_TIME)


              what we will do is run the 'cleanupHistoricalData' command for JobRunEvent, AuditTrail, AuditResult and SnapshotResult once we have the value of the retention time.  this can run online.

              after those are completing we will then run the executeRetentionPolicy, and then cleanupDatabase, hardDeleteAllSharedObjects and cleanupFileServer.  running each one until they complete.


              then we can go back and setup all of these to run on a schedule.


              note though that even after deleting data w/ cleanup the data file size will not change unless the dba does a shrink.  but you don't need to do that, just let the db re-use the space as it needs it.

              • 4. Re: BSA DB CLEANUP HELP !
                Edgar Lima

                Hello Bill.

                The retention policy is 600 days, and they had plus 2190 days of DB (6 years of BSA).

                We tried to clean on Snapshot Result and take to loonng to finish.

                • 5. Re: BSA DB CLEANUP HELP !
                  Bill Robinson

                  What version of 8.5 is this ?


                  There is offline cleanup that should run pretty quick, but you should be on 8.5.01 or later for that.  https://docs.bmc.com/docs/display/public/bsadoc/Runningofflinedatabase+cleanup


                  Can you take an outage to run that and reduce the retention time ?  why do they need ~2 years of data ?

                  • 6. Re: BSA DB CLEANUP HELP !
                    Edgar Lima

                    Its 8.5.1, we tried show the offline retention, but the client denied.

                    Offline cleaning only retains at maximum 90 days and we need a retention of at least 2 years(730 days ) and clean 4 years (1460 days ).





                    • 7. Re: BSA DB CLEANUP HELP !
                      Bill Robinson

                      there is no reason to have a retention policy of 2 years - what is the requirement?


                      have you opened a ticket on this ?


                      have you read through the document and looked at table sizes and such ?

                      • 8. Re: BSA DB CLEANUP HELP !
                        Edgar Lima

                        Hello Bill.

                        Yes, the customer have this internal politc. They are one of the biggest company on Brazil.

                        No, i dont, why should i open a ticket ?

                        Yes i did.


                        MB SIZE


                        SNAPSHOT_BLFILE              TABLE                        14375                                                              

                        SNAPSHOT_COM_OBJECT TABLE                        10556                                                              

                        BLFILE                                       TABLE                         6804                                                              

                        AUDIT_BLFILE                         TABLE                         6319                                                              

                        JOB_RUN_EVENT                  TABLE                         3008                                                              

                        AUDIT_COM_OBJECT           TABLE                         2377                                                              

                        BLFILE_CDC                            TABLE                         1093                                                              

                        AUDIT_TRAIL                           TABLE                          914    




                        ROW COUNTS

                        SNAPSHOT_BLFILE                             74853967                                                                                      

                        SNAPSHOT_COM_OBJECT              45994370                                                                                      

                        BLFILE_CDC                                         44837387                                                                                      

                        BLFILE                                                    42111893                                                                                      

                        AUDIT_BLFILE                                      40825790                                                  

                        AUDIT_COM_OBJECT                         13721450                                                                                      

                        JOB_RUN_EVENT                               10227034                                                                                      

                        DEPLOY_JOB_RUN_EVENT              6443316                                                                                      

                        AUDIT_TRAIL                                          4408507





                        *********DB SIZE IN MEGABYTES**********************



                        INDEX               36191.125                                                                                                  

                        TABLE              47783.9375                                                                                                  


                        • 9. Re: BSA DB CLEANUP HELP !
                          Edgar Lima

                          This weekeend we put a BLCLI commando to clean SNAPSHOT tables.

                          We put only 50days to cleanup, it already pass 50 hours and still cleaning.

                          How i cancel the blcli command ?


                          Thank you  all

                          • 10. Re: BSA DB CLEANUP HELP !
                            Bill Robinson

                            What did you run exactly ?


                            Why do you want to cancel it ?


                            I suggested opening a ticket so we can analyze the database and determine the best course forward to minimize problems while trying to catch up on cleanup.


                            The table info you put in the post was not complete and there’s a lot of other information we should gather first.


                            Also – the database is 83gb ?  that’s not very large….  Where did this 160gb come from ?

                            • 11. Re: BSA DB CLEANUP HELP !
                              Edgar Lima

                              Hello Bill.

                              We already done this steps:

                              SQL> select count(*) from depot_object where is_deleted = '1';
                              SQL> select count(*) from snapshot_blfile where is_deleted = '1';
                              SQL> select count(*) from job_run_event where is_deleted = '1';
                              SQL> select count(*) from blfile where is_deleted = '1';
                              SQL> select count(*) from audit_blfile where is_deleted = '1';
                              SQL> select count(*) from deploy_job_run_event where is_deleted = '1';

                              1) blcli_execute Delete cleanupHistoricalData AuditTrail
                              2) blcli_execute Delete cleanupHistoricalData JobRunEvent
                              3) blcli_execute Delete cleanupHistoricalData JobSchedule
                              4) blcli_execute Delete executeRetentionPolicy
                              5) blcli_execute Delete cleanupHistoricalData AuditResult
                              6) blcli_execute Delete cleanupHistoricalData ComplianceResult



                              And we are now on step 7

                              blcli_execute Delete cleanupHistoricalData SnapshotResult


                              Here is the problem.


                              The customer needs 2 years (700 days) of retention policy. So i configured the retention time to 700 and started the job (blcli_execute Delete cleanupHistoricalData SnapshotResult)

                              After 3 days, he still running and we canceled the job.

                              We tried to put 2150 days (6 years of BSA Enviroment = 2190 days) on retention time to reduce the time and after 70 hours the job still running.

                              Its a production enviroment and we have only 2 days window (wekend)  to execute the wave of DBCLENup.

                              The customers need to cancel because ends our gap. Its a production enviroment and we cant do the DPCLEANUP on week days. Only in weekends.

                              A question, is it a problem run BLCLI clean snapshotclean command with BSA working ? Like doing jobs, snapshot and etc ?

                              • 12. Re: BSA DB CLEANUP HELP !
                                Bill Robinson

                                These commands are meant to run when the environment is online.  why not let it run ?