The RSRCROLEASGNMENTSPREAD P6 Database Table Grows Rapidly (Doc ID 2642687.1)

Last updated on OCTOBER 12, 2023

Applies to:

Symptoms

The RSRCROLEASGNMENTSPREAD P6 database table grows exponentially when publishing Resource and Role Data.

• The RSRCROLEASGNMENTSPREAD table growth rate depends on the number of resources and role assignments and the frequency of publishing resource and role data.
• Duplicate records are created when publishing resource and role data, which are displayed in the JOBSVC table as job type JT_RsrcRoleSpread.
• Executing the following query as the schema owner (default admuser for Oracle, sa for SQL Server) or the reporting user (default pxrptuser) displays duplicate records similar to the following - note only the 'id' value changes.

select count(*) from RSRCROLEASGNMENTSPREAD WHERE proj_id is null and role_id is null and id is null AND rsrc-id=null;

id             proj_id  rsrc_id  role_id   spread_start_date                spread_end_date
311980  24731   7027      1583       2018-01-01 00:00:00.000    2018-01-02 00:00:00.000
311982  24731   7027      1583       2018-01-01 00:00:00.000    2018-01-02 00:00:00.000
311983  24731   7027      1583       2018-01-01 00:00:00.000    2018-01-02 00:00:00.000
311987  24731   7027      1583       2018-01-01 00:00:00.000    2018-01-02 00:00:00.000

The issue can be reproduced at will using the following steps:

1. Login to P6 Web as an admin user and navigate to Administration > Application Settings > Services.
2. Enable the publication of Resource and Role data by checking both Publication and the option Publish resource and role data.
3. Schedule all projects, then publish all projects manually or as a scheduled publication under Scheduled Services.
4. Note the rapidly increasing size of the RSRCROLEASGNMENTSPREAD table.

Changes

Cause