what cleanups are you running right now, w/ what frequency ?
what data base back end are you using ?
are you updating table stats on a regular basis ? (if oracle w/ the bmc provided procedure)
what version of bsa ?
Hi there Bill,
Well the picture below stands for the sctructure of jobs we run...
This follows the guidelines suggested in the official documentation.
We have set a retention policy of 60 days for all the modules... the most probelmatic one is the compliance module, in terms of data volume.
Our version of BSA is 8.5.01 P5... and yes, we run BMC statistics every week.
But if we can't find a cleanup pace which let us continuing slowly reducing its size we will have to run offline clenaup tasks in a near future. This is our worry.
Well I asked for running those scripts to BDA team. In the meantime I would like to mention that we used to run Daily task once a day from Monday to Friday and Weekly tasks twice on the weekends, Saturday and Sunday each one.
But we considered at some point to test this other possibility, removing all the related information to the daily task after its execution. So that, we saw its effects oor impact on the DB size returned by the Dashboard. And after comparing them I guessed seeing a higher reduction of the size in that way. But I was just testing.
But if you consider this is not a good idea... or you want to make a better proposal I will listen to you carefully.
sorry for the delay. a few notes:
last time you ran audit trail cleanup:
31332736,audit trail cleanup,1081021,2014-07-21 10:18:39,2014-07-21 11:48:18,2014-07-21 11:48:18,Completed,112790401
AUDIT_TRAIL is 75mn rows.
probably want to think about running audit_trail cleanup
job_run_event cleanup is running but there are still 75mn rows in there. what's the retention time? what's the breakdown of the data over time:
select count(message),trunc(event_date,'w') from job_run_event group by trunc(event_date,'w');
job_result_device table is also fairly large, the result cleanups haven't run for a while:
77385600,snap result cleanup,949,2015-07-14 12:22:06,2015-07-14 12:22:12,2015-07-14 12:22:12,Completed,4287583
(audit result ran, but it looks like it hadn't run for a while so maybe it's not completed):
Line 204: 77384699,audit result cleanup,,2015-06-29 12:10:34,2015-06-29 12:10:37,2015-06-29 12:10:37,Completed,4322856
Line 205: 114459570,audit result cleanup,,2015-11-29 11:57:43,2015-11-29 11:57:46,2015-11-29 11:57:46,Completed,4368188
also, data in that table could be associated w/ other job types - can you run:
select count(*),object_type_id from job_result group by object_type_id;
for the compliance result cleanup runs it looks like they are running long:
Line 191: 114489801,compliance result cleanup,56799066,2015-11-30 03:38:17,2015-11-30 14:30:09,2015-11-30 14:30:09,Completed,4357575
Line 192: 115192545,compliance result cleanup,47712939,2015-11-30 14:30:09,2015-12-01 00:28:05,2015-12-01 00:28:05,Completed,4361164
one thing you can do here is get some db reports (awr/addm) from the next run of the complianceResult cleanup and see if there are any issues.
you can post that here or open a support ticket.