Primavera P6 database has grown to a very large size

Question

I'm not a P6 admin, nor am I a (SQL Server) DBA. I'm just a Winforms developer (with T-SQL) who has agreed to do a little research for the scheduling group.

I believe the version they're running is 8.2, desktop (non-Citrix). Backend is SQL Server. The backend has grown to 36gb and nightly backups are periodically filling drives to their limits.

REFRDEL holds 135 million records, dating back to some time in 2012. UDFVALUE holds 26 million records

All other tables have reasonable numbers of records.

Can someone clue us in as to which of the several cleanup-oriented stored procedures to run (if any), or offer some sane advice so that we can get the backend down to a manageable size, please? Something that would not violate best practices and is considered very safe, please.

Answer 1

It is normal for UDFVALUE to hold a large number of records. Each value for any user-defined field attached to any object in P6 will be represented as a record in this table.

REFRDEL on the other hand should be automatically cleaned up during normal operation in a healthy system. In P6 8.x, they should be cleaned up by the data_monitor process, which by default is configured to run once a week (on Saturdays).

You should be able to execute it manually, but be forewarned: it could take a long time to complete if it hasn't executed properly since 2012.

36gb is still a very, very large database. For some clients a database of that magnitude might not be unreasonable depending on the total number of activities and, especially, the kinds of data that is stored. For example, notepads take comparatively a large amount of space.

In your case though, since you already know data_monitor hasn't executed properly for a while, it's more likely that the tables are full of records that have been soft-deleted but haven't yet been purged. You can see such records by running a query such as:

select count(*) from task where delete_session_id is not null;

Note that you must select from the task table, not the view, as the view automatically filters these soft-deleted records out.

You shouldn't delete such records manually. They should be cleaned up, along with the records in REFRDEL, as a result of running data_monitor.

Answer 2

A bit late coming to this, but thought the following may be useful to some. We noticed REFRDEL had grown to a large size and after some investigation discovered the following ...

DAMON runs the following procedures to perform clean-up:

• BGPLOG_CLEANUP
• REFRDEL_CLEANUP
• REFRDEL Bypass
• CLEANUP_PRMQUEUE
• USESSION_CLEAR_LOGICAL_DELETES
• CLEANUP_LOGICAL_DELETES
• PRMAUDIT_CLEANUP
• CLEANUP_USESSAUD
• USER_DEFINED_BACKGROUND

DAMON was configured to run every Saturday around 4pm but we noticed that it had been continuously failing. This was due to an offline backup process which started at 10pm. We first assumed that this was preventing the REFRDEL_CLEANUP from running.
However after monitoring REFRDEL for a couple of weeks, we found that REFRDEL_CLEANUP was actually running and removing data from the table. You can check your table by running the following query on week 1 and then again in week 2 to verify the oldest records are being deleted.

select min(delete_date), max(delete_date), count(*) from admuser.refrdel;

The problem is to do with the default parameters used by the REFRDEL_CLEANUP procedure. These are described here but in summary the procedure is set to retain the 5 most recent days worth of records and delete just 1 days' worth of records. This is what's causing the issue...DAMON runs just once a week...and when it runs the cleanup job, it's only deleting 1 day's data but has accumulated a week's worth...therefore the amount of data will just get bigger and bigger.
The default parameters can be overridden in the SETTINGS table.
Here are the steps I took to correct the issue:
First, clean up the table..

-- 1. create backup table
CREATE TABLE ADMUSER.REFRDEL_BACKUP TABLESPACE PMDB_DAT1 NOLOGGING AS
Select * from admuser.refrdel where delete_date >= (sysdate - 5);

-- CHECK DATA HAS BEEN COPIED


-- 2. disable indexes on REFRDEL
alter index NDX_REFRDEL_DELETE_DATE unusable;
alter index NDX_REFRDEL_TABLE_PK unusable;

-- 3. truncate REFRDEL table
truncate table admuser.refrdel;

-- 4. restore backed up data
ALTER TABLE ADMUSER.REFRDEL NOLOGGING;
insert /*# append */ into admuser.refrdel select * from admuser.refrdel_backup;
--verify number of rows copied
ALTER TABLE ADMUSER.REFRDEL LOGGING;

commit;

-- 5. rebuild indexes on REFRDEL
alter index NDX_REFRDEL_DELETE_DATE rebuild;
alter index NDX_REFRDEL_TABLE_PK rebuild;

-- 6. gather table stats
exec dbms_stats.gather_table_stats(ownname => 'ADMUSER', tabname => 'REFRDEL', cascade => TRUE);

-- 7. drop backup table
drop table admuser.refrdel_backup purge;

Next, override the parameters so we try to delete at least 10 days' worth of data. The retention period will always keep 5 days' worth of data.

exec settings_write_string(‘10',’database.cleanup.Refrdel’,’DaysToDelete’);   -- delete the oldest 10 days of data
exec settings_write_string(’15’,’database.cleanup.Refrdel’,’IntervalStep’);   -- commit after deleting every 15 minutes of data
exec settings_write_string(‘5d’,’database.cleanup.Refrdel’,’KeepInterval’);   -- only keep 5 most recent days of data

This final step is only relevant to my environment and will not apply to you unless you have similar issues. This is to alter the start time for DAMON to allow it complete before our offline backup process kicks in. So in this instance I have changed the start time from 4pm to midnight.

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   name         =>  'BGJOBUSER.DAMON',
   attribute    =>  'start_date',
   value        =>  TO_TIMESTAMP_TZ('2016/08/13 00:00:00.000000 +00:00','yyyy/mm/dd hh24:mi:ss.ff tzr'));
END;
/

Answer 3

When you look at the data in the database there is a column name "delete_session_id". Do you see any with the value of -99? If so, then there is an unresolved issue on this. If not, then proceed with the following to get the clean up jobs running again...

If you are using SQL Server (Full Editions), perform the following steps to resolve the issue:

1. Verify that the SQL Server Agent service is started on the server and has a startup type of automatic.

   • Logs for this service can be found (by default) at:
   • C:\Program Files\Microsoft SQL Server\\LOG\SQLAGENT.x
   • This log includes information on when the service was stopped/started

2. If the SQL Agent is started, you can then check what jobs exist on the SQL Server database by issuing the following command as SA through SQL Query Analyzer (2000) or through Microsoft SQL Server Management Studio:

   • select * from msdb.dbo.sysjobs

3. If the Primavera background processes (SYMON and DAMON) are not listed, or the SQL Agent was not started, then these background processes can be reinitialized by running the following commands as SA user against the Project Management database:

   • exec initialize_background_procs
   • exec system_monitor
   • exec data_monitor