Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Lock exhaustion in DB caused by SPPR_PURGE_TABLE Stored Proc

SA-1076

Summary



Administrator has reported ‘lock exhaustion exceptions’ in the database caused by SPPR_PURGE_TABLE stored procedure while deleting large amounts of data from the table PR_PERF_STATS.

Error Messages



Messages were written to database console:
10.49.45 STC24484 *DXR142E IRN2002 THE LOCK STRUCTURE DB2N_LOCK1 IS 080% I
10.49.51 STC24484 *DXR142E IRN2002 THE LOCK STRUCTURE DB2N_LOCK1 IS 100% I


Steps to Reproduce



Unknown

Root Cause



System Cleaner agent was not working in the issue-reported environment and it was not deleting the records.


Resolution



By default, Pega automatically purges records older than 30 days. If you do not need older rows for performance analysis, debugging, or other reporting, you can purge them by date (known as trimming) without affecting other system capabilities. Once each day, the Pega-RULES agent starts a stored procedure that purges older rows of this table, passing the value of the pxProcess property as the number of days to retain.

To set a retention period longer or 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.

Perform the following local-change:
  1. Back up the PR_PERF_STATS table from the behaviour reported environment.
  2. Delete a large number of records from this table: Truncate the PR_PERF_STATS table.
  3. Set the appropriate usage retention period by adding a prconfig.xml entry, <env name="usage/retentionperiod " value="nnn" />.
  4. Restart the server.
See also this Help topic:
https://community.pega.com/sites/default/files/help_v62/procomhelpmain.htm#howto/howto2/logusagetable.htm
 
Suggest Edit

Published August 1, 2018 - Updated October 8, 2020

Did you find this content helpful? Yes No

100% found this useful

Have a question? Get answers now.

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

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us