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…
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.
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.
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 ?
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 ?
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.
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
*********DB SIZE IN MEGABYTES**********************
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
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 ?
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 SnapshotResultHere 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 ?
These commands are meant to run when the environment is online. why not let it run ?