Support Article

Long running stored procedure due to PZSYSTEMSTATUSPURGE.

SA-37796

Summary



The purge procedure is throwing many DB alerts every week

{call PEGADATAO.sppr_purge_table (?,?,?)}

SP is called by this rule --> RULE-OBJ-ACTIVITY SYSTEM-STATUS-DETAILS PZSYSTEMSTATUSPURGE #20150924T142248.666 GMT


Error Messages

--- from PegaALERTS log -----
2017-05-01 04:00:45,555 GMT*8*PEGA0005*20210*500*52f8cf0dff2656fbb7a2012b3e7c1034*NA*NA*BD7A7E0515D0BB44CD69039FE7A8398C1*System*PegaMKT-Work*App:01.01.01*ec254452ff6b5af97299f0b9127f8e81*N*1*BD7A7E0515D0BB44CD69039FE7A8398C1*6036*PegaRULES-Batch-132*STANDARD*com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl*NA*Code-.SystemCleaner*Rule-Obj-Activity:SystemCleaner*SYSTEM-STATUS-DETAILS PZSYSTEMSTATUSPURGE #20150924T142248.666 GMT Step: 2 Circum: 0* *pxTotalReqTime=45.55;pxInteractions=1;pxOtherBrowseReturned=8;pxAlertCount=3;pxRulesUsed=18;pxOtherIOElapsed=4.59;pxRulesExecuted=13;pxListWithoutStreamCount=1;pxRuleIOElapsed=0.00;pxRDBWithStreamCount=1;pxRuleCount=1;pxRDBRowWithStreamCount=9;pxOtherIOCount=6;pxDBInputBytes=40,797;pxRDBIOCount=2;pxTotalReqCPU=0.04;pxOtherBrowseElapsed=0.18;pxDBOpExceedingThresholdCount=2;pxListRowWithoutStreamCount=8;pxOtherFromCacheCount=2;pxOtherCount=5;pxActivityCount=13;pxRDBIOElapsed=0.17;pxRuleFromCacheCount=1;*NA*NA*NA*NA*java;RULE-OBJ-ACTIVITY SYSTEM-STATUS-DETAILS PZSYSTEMSTATUSPURGE #20150924T142248.666 GMT Step: 2 Circum: 0;doActivity Rule-Obj-Activity:pzSystemStatusPurge;Call System-Status-Details.pzSystemStatusPurge;RULE-OBJ-ACTIVITY CODE- SYSTEMCLEANER #20151021T210454.895 GMT Step: 17 Circum: 0;2 additional frames in stack;*pyTempPlaceHolder=TempPlaceHolder;pxObjClass=;*Database operation took more than the threshold of 500 ms: 20,210 ms SQL: {call PEGADATAO.sppr_purge_table (?,?,?)}*


Steps to Reproduce



Daily execution of SystemCleaner by agent Pega-RULES.


Root Cause



The alert seen here is coming from pzSystemStatusPurge activity, which is called from SystemCleaner activity. These alerts are happening around the same time every day; which should coincide with the scheduling. The performance of this functionality was upgraded in 7.1.10. A busy multi-node system could generate nearly 10,000 records on a daily basis.

Resolution



Control this cleanup by configuring "DSS management/systemdetails/maxdays shorter than 7 days". If this DSS is not set, system will default to 7 days. Alternatively, run this activity independently to clean up a backlog.

Published May 10, 2017 - Updated May 11, 2017

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.