In the first article on this subject that you can read here, we introduced the role of the Primavera P6 EPPM Background Jobs which included the removal of records from the database that were no longer required. These are the records of deleted items such as Activities. Primavera P6 doesn’t actually delete these records, choosing to mark them as deleted instead. The Background Jobs come along once a week and are supposed to delete all these records.
Despite there being 7 days in a week, the Primavera P6 EPPM Background Jobs are configured to delete just 1 day per week, leaving 6 days of deleted data inside the database.
The first article showed how to change the settings for the REFRDEL table to remove the unwanted records. One of our customers had over 70 million records in their REFRDEL table. That used one set of parameters for removing the records. In this article we’ll discuss the set of parameters that are used by other tasks in the Background Jobs. These are configured to delete just 1 day’s worth and we’ll show how to change them.
Fixing CLEANUP_USESSAUD and CLEANUP_PRMQUEUE
The CLEANUP_USESSAUD task removes old audit records for user sessions. This removes records from the USESSAUD table and was standing at over 32 million records for our client. The CLEANUP_PRMQUEUE task removes completed EPS and project security tasks, which were nowhere near the same number of records, but was still configured such that it doesn’t remove all the unwanted records every week. Both of these jobs have the same set of control parameters, which are different to the REFRDEL cleanup task from the last article.
They have five settings, and is more complicated than the REFRDEL_CLEANUP task.
The “KeepInterval” setting defaults to “5d”. This is the same as for REFRDEL_CLEANUP. It is the number of days that will not be deleted before the day the Background Jobs are being ran. The day the Background Jobs are being ran is also kept from being deleted.
The “DeleteAll” setting defaults to “0”. When this setting is set to “1” then all the records are deleted apart from those protected by the “KeepInterval” setting above.
The “DeletePercentage”, “DeleteAllThreshold” and “MaxRowsToDelete” settings are related to each other. The “DeletePercentage” setting defaults to “10” which selects 10% of the rows that are not protected by the “KeepInterval” setting. The “DeleteAllThreshold” setting is set to 1,000 and if this is more than the number of rows calculated by the “DeletePercentage” setting, then this becomes the number of rows to be deleted. The final setting is the “MaxRowsToDelete” setting which defaults to 10,000 and if the number of rows calculated using the “DeletePercentage” is more than this then it is reduced to match it.
Using the default settings then somewhere in between 1,000 and 10,000 records are deleted using these two jobs each week. This will not cause a problem with the default settings unless there are more than 1,000 records that are not protected by the “KeepInterval” setting. Then there will be records inside the databse that may or may not be deleted next time. Experience indicates that with just 100 users the problem will occur, and often much less.
The preferred choice on new systems is to set the “DeleteAll” setting to “1” so all unprotected records are deleted every time. However, this will not be possible when the P6 implementation has been around for some time, and like our client, there are many millions of records in there. The database should not spend many hours just deleting records. We use SQL to determine roughly how many records are being added each week. Having worked that out, we’d update the settings to delete a month or more worth of records each week.
As an example if we calculated there were 200,000 records being created each week then we’d want to delete 800,000 records each week. To do that we’d set the “DeletePercentage” to “100”, the “DeleteAllThreshold to 799,999 and the “MaxRowsToDelete” would be 800,000. That way either 80,000 or 1 less than that records would be deleted each week.
Here is the code we used to remove the backlog at our client. Once the backlog had gone, which took a little over three months, the “DeleteAll” setting was changed to 1.
exec settings_write_string(‘8d’,’database.cleanup.Usessaud’,’KeepInterval’);
exec settings_write_bool(0,’database.cleanup.Usessaud’,’DeleteAll’);
exec settings_write_number(799999,’database.cleanup.Usessaud’,’DeleteAllThreshold’);
exec settings_write_number(100,’database.cleanup.Usessaud’,’DeletePercentage’);
exec settings_write_number(800000,’database.cleanup.Usessaud’, ‘MaxRowsToDelete’);
Fixing CLEANUP_LOGICAL_DELETES
There is no need to dwell too much on this last one. This task has the same settings as CLEANUP_USESSAUD and CLEANUP_PRMQUEUE but without “DeletePercentage” and “DeleteAllThreshold”. Here there are two choices. Setting “DeleteAll” to “1” will delete everything outside of the “KeepInterval” setting. This is the setting for new installations and the one to aim for where the implementation has been around for a while. The alternative is to set “DeleteAll” to “0” and “DeleteAllThreshold” to the number of records to delete each run. If you have 500,000 records to delete and want to delete them all in 10 weeks, then set “DeleteAllThreshold” to something above 50,000 to capture the records for each week as well. If there are an extra 1,000 each week on average then I’d set it to 52,000 just to be sure.
Cleaning up faster
For those who don’t want to wait a number of months there is an alternative. Having set the values up to delete over a number of months, simply put the DATA_MONITOR procedure in a loop and let it run.
For more Primavera P6 articles click here