My Oracle Support Banner

HR_DRPT_BLD Fails When Inserting Into PS_HR_DIRECT_REP_2 Because Of Lack Of TEMPDB Space (Doc ID 2535759.1)

Last updated on NOVEMBER 26, 2021

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Other

HR_DRPT_BLD fails when inserting into PS_HR_DIRECT_REP_2 because of lack of TEMPDB space

This process ran with no problems for months, then all of a sudden is now consuming large amounts of TEMPDB space and failing.

We have rebuilt indexes that are involved in the process. Still fails. We have reorganized and updated stats. Still fails.

The Peoplesoft SQL database data file is approx. 22GB. The current TEMPDB data file is 125GB. I would not expect TEMPDB to need to be 6 times the size of the actual PS database.

Please offer advice on resolving this issue.

ERROR
-----------------------
File: e:\pt85324a-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1625 Error Position: 0 Return: 8601 - [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files
Failed SQL stmt: INSERT INTO PS_HR_DIRECT_REP_2 (SUPERVISOR_ID , EMPLID , EMPL_RCD , EMPL_STATUS , POSITION_NBR , JOB_EFFDT , HR_DR_LEVEL , SUPERVISOR_FLAG , DRILL_DOWN_FLAG) SELECT DISTINCT SUPER.SUPERVISOR_ID , INDIR.EMPLID , INDIR.EMPL_RCD , INDIR.EMPL_STATUS , INDIR.POSITION_NBR , INDIR.JOB_EFFDT , SUPER.HR_DR_LEVEL + INDIR.HR_DR_LEVEL , INDIR.SUPERVISOR_FLAG , CASE SUPER.DRILL_DOWN_FLAG WHEN 'N' THEN 'N' ELSE INDIR.DRILL_DOWN_FLAG END FROM PS_HR_DIRECT_REP_2 SUPER , PS_HR_DIRECT_REP_2 INDIR WHERE SUPER.EMPLID = INDIR.SUPERVISOR_ID AND NOT EXISTS ( SELECT 'X' FROM PS_HR_DIRECT_REP_2 DR2 WHERE DR2.EMPLID = INDIR.EMPLID AND DR2.EMPL_RCD = INDIR.EMPL_RCD AND DR2.SUPERVISOR_ID = SUPER.SUPERVISOR_ID)

Process 99815 ABENDED at Step HR_DRPT_BLD.2BySuper.Step04 (SQL) -- RC = 8601 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

PSAESRV completed service request at 02.35.10 2019-03-19





STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. run the AE_HR_DRPT_BLD process
2. observe that the process errors-out with 'C ould not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files'



Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.