SA-32558

Summary

System cleaner is not cleaning up perf stats table. Exceptions are found in Pega Logs.

Error Messages

2016-12-14 00:00:10,423 [ PegaRULES-Batch-2] [ STANDARD] [ ] [ PegaRULES:07.10] (l.access.ConnectionManagerImpl) ERROR - Not returning connection 1 for database "pegadata" to the pool as it previously encountered the following error
User ID: System
Last SQL: {call PEGA_WORK.sppr_purge_table (?,?,?)}
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 185) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1522)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1716)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:576)
at com.pega.pegarules.data.internal.access.SQLGenerator.callStoredProc(SQLGenerator.java:3244)
at com.pega.pegarules.data.internal.access.DatabaseImpl.callStoredProc(DatabaseImpl.java:10110)
at com.pega.pegarules.data.internal.access.SQLGenerator.purgeTableContainingClass(SQLGenerator.java:2871)
at com.pega.pegarules.data.internal.access.DatabaseImpl.purgeTableContainingClass(DatabaseImpl.java:6825)
at com.pegarules.generated.activity.ra_action_pzsystemstatuspurge_5b2e9e10ae5b0e174dfcd127853ce2ee.step2_circum0(ra_action_pzsystemstatuspurge_5b2e9e10ae5b0e174dfcd127853ce2ee.java:218)
at com.pegarules.generated.activity.ra_action_pzsystemstatuspurge_5b2e9e10ae5b0e174dfcd127853ce2ee.perform(ra_action_pzsystemstatuspurge_5b2e9e10ae5b0e174dfcd127853ce2ee.java:86)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10450)
at com.pegarules.generated.activity.ra_action_systemcleaner_086584e6b8a1ac0ea644fcb3ea53969f.step16_circum0(ra_action_systemcleaner_086584e6b8a1ac0ea644fcb3ea53969f.java:1142)
Same error is seen for pr_log_reportstats and pr_sys_statusdetails tables.

Steps to Reproduce

Not Applicable

Root Cause

A defect or configuration issue in the operating environment.

System Cleaner is unable to delete records from pr_perf_stats table as there are more than 2 million records that have accumulated in this table.

The database is configured with more than 20 live JVMs. Every Requestor, Agent, CTI from each JVM writes performance data to this table every hour and upon termination/signoff.

Resolution

Make the following change to the operating environment:

Delete rows from these tables to bring the size down to thousands records so that the Pega stored proc can execute subsequent deletes without taking too long.

Also perform the following local-change:

Set this prconfig and the other two DSS after deleting records from these tables (pr_log_reportstats, pr_perf_stats, pr_sys_statusdetails) that are older than say 7 days.

<env name="usage/retentionperiod" value="7" />

Owning Ruleset: Pega-RulesEngine
Purpose: management/systemdetails/maxdays
Value: 7

Owning Ruleset: Pega-RulesEngine
Purpose: reporting/reportstatistics/minimumDaysToPurgeFromDatabase
Value: 7

This should bring the tables to manageable sizes and also allow System Cleaner to maintain it.