PrimaverArena.com | Your best Primavera P6 source on the web

The P5 PURGE DELETES function runs two stored procedures: 

1. Cleanup_Logical_Deletes – removes data table records that are logically deleted with delete_session and delete_date entries older than the last active user session.


2. Referdel_Cleanup – removes REFRDEL table entries older than the last active user session.

NOTE:  The process to remove logically deleted records is NOT dependent on REFRDEL entries being present, so the two stored procedures can be run independently.

NOTE:  It is recommended that everyone be out of the database before running either of the scripts, as this can cause a performance hit.

To manually execute PURGE DELETES in Project Management verion 5 on the database server using scripts, execute scripts as described below for CLEANUP_LOGICAL_DELETES and REFRDEL_CLEANUP

CLEANUP_LOGICAL_DELETES – To manually execute CLEANUP_LOGICAL_DELETES perform the following:

For ORACLE:

- Execute the following SQL through SQLPLUS as ‘admuser’ :

variable ret number
variable msg varchar2(1000)
exec cleanup_logical_deletes(0,:ret,:msg,0);

To run a timed script:

- Connect to the database via SQLPLUS as ADMUSER run the attached script –>  cleanup_logical_deletes.sql   which contains the following:

var jobid number
exec dbms_job.submit(:jobid, ‘declare r number; m varchar2(4000); begin cleanup_logical_deletes(0,r,m,0); end;’,TRUNC(SYSDATE, ‘MI’)+1/1440, ‘TRUNC(SYSDATE+1,”DD”)’);
print jobid

NOTE: This script will create an Oracle Job to run the Stored Procedure in the database to purge deletes every day at Midnight.

For MS SQL SERVER:

- Execute the following SQL through SQL Query Analyzer as ‘privuser’ or ’sa’ :

declare @ret integer, @msg varchar(255)
BEGIN
Exec cleanup_logical_deletes 0, @ret output, @msg output
END

NOTE:  For both Oracle and SQL Server, a database job can be setup to automate the above commands on a regular interval.  Ideally, the job should be run during non-production hours (EX: Daily at 1:00am).

 

REFRDEL_CLEANUP - To manually execute REFRDEL_CLEANUP peforming the following:

For ORACLE:
 
To run immediately:

- Login into SQLPLUS as ‘privuser’ or ‘admuser’

- Run the following commands at the SQL> prompt

variable ret number
variable msg varchar2(2000)
exec refrdel_cleanup (0,:ret,:msg);

To run a timed script:

- Connect to the database via SQLPLUS as ADMUSER run the attached script –>  refrdel_cleanup_job.sql   which contains the following:

var jobid number
exec dbms_job.submit(:jobid, ‘declare r number; m varchar2(4000); begin refrdel_cleanup(0,r,m); end;’,TRUNC(SYSDATE, ‘MI’)+1/1440, ‘TRUNC(SYSDATE+1,”DD”)’);
print jobid

NOTE: This script will create an Oracle Job to run the Stored Procedure in the database to purge deletes every day at Midnight.

(OPTIONAL)
To verify that the automated procedure ran successfully:
- Login into SQLPlus as Admuser
- Run the following query:

SQL> select last_date, next_date, failures, what from user_jobs; 

For SQL Server:

To run immediately, execute the following through SQL Query Analyzer as ‘privuser’ or ’sa’

declare @ret integer, @msg varchar(255)
BEGIN
exec refrdel_cleanup 0, @ret output, @msg output
END

To create run a timed script, create a  Stored Procedure similar to below:

declare @ret integer, @msg varchar(255)
while ( 1=1 )
begin
exec REFRDEL_CLEANUP 0,@ret output, @msg output
WAITFOR DELAY ‘000:01:00′
end 

NOTE:  This procedure will execute the Stored Procedure REFRDEL_CLEANUP every hour.

NOTE:  For both Oracle and SQL Server, by default, the Primavera Background Jobs will execute these procedures above every 5 minutes.
 See also Solution ID prim57333 - ”Where is Purge Deletes in P6? / What are the SYMON (System Monitor) and DAMON (Data Monitor) processes in P6 (Project Management 6.0)?”  

CLEANUP_LOGICAL_DELETES – To manually execute CLEANUP_LOGICAL_DELETES perform the following:

For ORACLE:

- Execute the following SQL through SQLPLUS as ‘admuser’ :

variable vret number;
variable vmsg varchar2(4000);
variable verror_level number;
exec cleanup_logical_deletes(:vret, :vmsg, :verror_level);

For MS SQL SERVER:

- Execute the following through SQL Management Studio as ’sa’ :

declare @pret_val integer, @pret_msg varchar(1000), @errorlevel int, @days int
BEGIN
Exec cleanup_logical_deletes @pret_val output, @pret_msg output
END

REFRDEL_CLEANUP - To manually execute REFRDEL_CLEANUP peforming the following:

For ORACLE:

- Execute the following SQL through SQLPLUS as ‘admuser’ :

variable vret number;
variable vmsg varchar2(4000);
exec refrdel_cleanup(:vret, :vmsg);

For SQL Server:

- Execute the following through SQL Management Studio as ’sa’

declare @ret integer, @msg varchar(255)
BEGIN
exec refrdel_cleanup @ret output, @msg output
END