Support Article
Getting PEGA0005 alerts for Log-RuleUsage purge sppr_purge_table
SA-81499
Summary
PEGA0005 alerts are generted for the Log-RuleUsage purge sppr_purge_table.
Error Messages
*8*PEGA0005*51826*500*f15588d3515b4762e5c02f2c26064e64*NA*NA*B9CF7B89A8AEC7E4595915487F8247AA2*System*PegaSample*PegaRULES:07.10*b0f61728d0c0235b9979c284b05ec554*N*1*B9CF7B89A8AEC7E4595915487F8247AA2*277*WorkManager.PegaDATADefaultSchema : 1*STANDARD*com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl**Code-.SystemCleaner*Rule-Obj-Activity:SystemCleaner*LOG-USAGE USAGECLEANER Step: 5 Circum: 0******pxListWithUnfilteredStreamCount=1;pxOtherBrowseElapsed=0.01;pxOtherBrowseReturned=44;pxInteractions=1;pxDeclarativeRulesLookupCount=20;pxListWithoutStreamCount=1;pxTotalReqCPU=0.87;pxRulesUsed=12;pxRulesExecuted=8;pxDeclarativeRulesLookupElapsed=0.00;pxListRowWithoutStreamCount=44;pxListRowWithUnfilteredStreamCount=7;pxOtherCount=8;pxOtherIOElapsed=0.12;pxRuleBrowseElapsed=0.00;pxDBOutputBytes=6,834;pxDBInputBytes=97,611;pxDeclarativeNtwksBuildHLElapsed=0.00;pxTotalReqTime=53.03;pxActivityCount=8;pxOtherIOCount=19;pxAlertCount=1;*NA*NA*NA*NA*Java;RULE-OBJ-ACTIVITY LOG-USAGE USAGECLEANER Step: 5 Circum: 0;doActivity Rule-Obj-Activity:UsageCleaner;Call Log-Usage.UsageCleaner;RULE-OBJ-ACTIVITY CODE- SYSTEMCLEANER Step: 12 Circum: 0;2 additional frames in stack;*pyTempPlaceHolder=TempPlaceHolder;*Database operation took more than the threshold of 500 ms: 51,826 ms SQL: {call PEGA_DATA.sppr_purge_table (?,?,?)}*
Steps to Reproduce
Execute the SystemCleaner agent.
Root Cause
A defect in Pegasystems’ code or rules.
Rule-LogUsage Data Purge stored procedure SQL has performance issues and intermittently executes for more than 50 seconds. This caused the PEGA0005 alerts on a daily basis.
While executing the SystemCleaner agent, the UsageCleaner activity is invoked in which the Log-RuleUsage purge step is executed.
SQL: {call PEGA_DATA.sppr_purge_table (?,?,?)}*
Resolution
Here's the explanation for the reported behavior:
By default, Pega automatically purges records that are older than 30 days.
If older rows are not required for performance analysis, debugging, or other reporting, purge the rows by Date (known as Trimming) without affecting the other system capabilities.
Once each day, the Pega-RULES agent starts a stored procedure that purges older rows of this table and passes the pxProcess property value as the number of days to retain.
To set a Retention period that is longer or shorter than 30 days, add or revise the following element to the prconfig.xml file and restart the system:
<env name="usage/retentionperiod " value="nnn" /> where nnn is the number of days to retain.
Perform the following local-change:
- Back up the PR_PERF_STATS table from the behavior reported environment.
- Delete a large number of records from the below table,
Truncate the PR_PERF_STATS table
- Add a prconfig.xml entry to set the appropriate usage Retention period,
<env name="usage/retentionperiod " value="nnn" />
- Restart the server.
For more information, refer to the Help topic: https://community.pega.com/sites/default/files/help_v62/procomhelpmain.htm#howto/howto2/logusagetable.htm
Published August 19, 2019 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.