Support Article
Many Pega0005 alerts on Pega-provided database tables
SA-20495
Summary
After you upgrade the system to the Pega 7 Platform, you notice that the following query runs several hundred times a day, increasingly degrading performance and blocking database retrieval with backed-up queries in queue.UPDATE dbo.pr_sys_statusdetails SET pxType='interval' WHERE pxType = 'current' AND pySysNodeId = @P0 AND pyNodeName = @P1 AND pySystemName = @P2
Error Messages
Pega0005 alerts
Steps to Reproduce
- Upgrade to the Pega 7 Platform.
- Observe many Pega0005 alerts on database tables, degraded performance, and backed-up queries blocking database retrieval.
Root Cause
A defect in Pegasystems’ code causes the purge of the pr_sys_statusdetails table to not work correctly.
Examination of the pr_sys_statusdetails table shows that it contains old records since the upgrade that are not being purged by the system cleaner agent. Over 600,000 rows exist in the pr_sys_statusdetails table.
Resolution
Perform the following local changes to resolve the issue.
| Table Name | Description |
| PR_DATA_RECENTS |
Create pyMaxRecentsCount Dynamic System Setting rule in the Pega-UIEngine' ruleset and set its value to 0 (zero) to not update the database. |
| PR_PERF_STATS |
Instances of the Log-Usage class, which (in the initial PegaRULES database schema) are stored in the pr_perf_stats table, support system-performance and usage reporting. The instances in this class provide a cumulative history of past system usage across all nodes and all requestor types. Analyses of this data can provide valuable insights about the patterns and sources of processing demand, and can be helpful in performance tuning. If it is important to keep the information for statistics, copy the content into an external table and then truncate the pr_perf_stats. In the future, follow these steps: Set a retention period shorter than the 30 days, add or revise the following element to your prconfig.xml file and restart the system: <env name="usage/retentionperiod " value="nnn" /> where nnn is the number of days to retain." |
| PR_SYS_STATUSDETAILS | Apply HFix-25231. |
| SPPR_SYS_RESERVEQUEUEITEM_B | Replace orderby := 'order by pzInsKey ASC'; clause with the below orderby := 'order by pyMinimumDateTimeForProcessing ASC' |
Published April 4, 2016 - 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.