000029613 - Size of tblDirtyCalcContent is large - Clean the table to reduce the size of the database

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000029613
Applies ToRSA Product Set : Archer

RSA Version/Condition : 5.x

Platform : Windows
IssueThe size of the tblDirtyCalcContent is increasing and is taking up a lot of db space
ResolutionTblDirtyCalcContent maintains a record of the all content ids that have not yet been recalculated / are yet to be recalculated by a job.
It is possible that these rows were populated when 'Full application' recalc was launched and somehow the recalc job failed. But did not clean up the tblDirtyCalcContent.
The below script should delete the unwanted records from this table:

 
DECLARE @BatchSize INT
SET @BatchSize = 1000

WHILE @BatchSize <> 0
BEGIN
delete TOP (@BatchSize) from tblDirtyCalcContent where job_num_id NOT IN ( select job_num_id from tblAsyncJobQueue)
SET @BatchSize = @@rowcount
END

Note: This will delete only the rows that do not have a valid job associated with them.

If the above script is not able to delete enough rows to save space, then it is because there are calc jobs yet to be run by the job engine. 
At this point, we can either:
- Wait for the job engine to process the calc jobs and the calc jobs in turn will delete rows from this table as they process.
OR
- Kill the calc jobs using the below script:

 
delete tblAsyncJobHold where BlockedJobId in(select jobid from tblAsyncJobQueue where JobType like '%ArcherTech.JobPackages.CalculationService.PhysicalCalculationsWorkflow%');
delete tblAsyncJobHold where TargetJobId in(select jobid from tblAsyncJobQueue where JobType like '%ArcherTech.JobPackages.CalculationService.PhysicalCalculationsWorkflow%');
delete tblAsyncJobRelationshipData where ParentJobId in(select jobid from tblAsyncJobQueue where JobType like '%ArcherTech.JobPackages.CalculationService.PhysicalCalculationsWorkflow%');
delete tblAsyncJobQueue where JobType like '%ArcherTech.JobPackages.CalculationService.PhysicalCalculationsWorkflow%';

- Once the calc jobs are deleted from the queue; run the below script to clear the tblDirtyCalcContent:
DECLARE @BatchSize INT
SET @BatchSize = 1000

WHILE @BatchSize <> 0
BEGIN
delete TOP (@BatchSize) from tblDirtyCalcContent where job_num_id NOT IN ( select job_num_id from tblAsyncJobQueue)
SET @BatchSize = @@rowcount
END

This will delete all the records from the table. Now, all our records will not be up to date and in order to recalculate them, we need to launch full application recalc. But launch these recalcs one at a time, so that we don't over burden the job engine and tbldirtycalccontent table again.
As and when the full recalcs complete, the tbldirtycalccontent table will be back to 0 giving us a stable calc condition.

Attachments

    Outcomes