UIM SSRV2AuditTrail and SSRV2AuditTrailModification tables are growing in size.
Article ID: 106271
Updated On:
Products
Issue/Introduction
The UIM MCS SSRV2AuditTrail and SSRV2AuditTrailModification tables are growing in size. They are filling the drive and causing the drive to run out of storage space. How can it be reduced in size?
Environment
Release: UIM 20.4.x and 23.4.x
Cause
The garbage cleanup query used to clean the audit trail tables is timing out due to the size.
Resolution
The SSRV2AuditTrail history can be safely removed.
This script will empty the SSRV2AuditTrail and SSRV2AuditTrailModification tables used to store this history.
USE <your database name>;
TRUNCATE TABLE SSRV2AuditTrailModification;
ALTER TABLE SSRV2AuditTrailModification DROP CONSTRAINT FK_SSRV2AuditTrailModification_SSRV2AuditTrail;
TRUNCATE TABLE SSRV2AuditTrail;
ALTER TABLE SSRV2AuditTrailModification WITH NOCHECK ADD CONSTRAINT FK_SSRV2AuditTrailModification_SSRV2AuditTrail FOREIGN KEY(auditrecord)
REFERENCES SSRV2AuditTrail(id);
ALTER TABLE SSRV2AuditTrailModification NOCHECK CONSTRAINT FK_SSRV2AuditTrailModification_SSRV2AuditTrail;
Additional Information
mon_config_service-8.5.8-HF4 and above have garbage collection of the MCS SSRV2Audittrail and SSRV2AudittrailModification tables, and will clear out table data on a periodic basis.
The following errors may be present in the mon_config_service.log if it is unable to clear out the table data via the normal processes:
Jul 27 06:59:31:834 [Thread-112239, mon_config_service] AuditTrailController.cleanupGarbage:359: STARTING audit garbage collection
Jul 27 06:59:31:835 [Thread-112239, mon_config_service] BasicDataSourceWrapper.getConnection:25: Get database connection took 1 ms
Jul 27 07:16:35:222 [Thread-112239, mon_config_service] An internal object pool swallowed an Exception.
Jul 27 07:16:35:223 [Thread-112239, mon_config_service] org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [ delete from ssrv2audittrail where id in ( select top(?) st.id from ssrv2audittrail st where st.timestamp < ? order by st.timestamp asc )]; SQL state [HY008]; error code [0]; The query has timed out.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:980)
at com.ca.uim.ce.selfservice.audit.database.AuditTrailDao.cleanupGarbage(AuditTrailDao.java:227)
at com.ca.uim.ce.selfservice.audit.controller.AuditTrailController.cleanupGarbage(AuditTrailController.java:361)
at sun.reflect.GeneratedMethodAccessor60.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.nimsoft.nimbus.NimProbeBase.activateOnTimer(NimProbeBase.java:411)
at com.nimsoft.nimbus.NimProbeBase.access$000(NimProbeBase.java:9)
at com.nimsoft.nimbus.NimProbeBase$OnTimerExecuteThread.run(NimProbeBase.java:454)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
at com.microsoft.sqlserver.jdbc.TDSCommand.checkForInterrupt(IOBuffer.java:7342)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:73)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1531)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:356)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:916)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:909)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
... 11 more
Jul 27 07:16:35:223 [Thread-112239, mon_config_service] AuditTrailController.cleanupGarbage:365: FINISHED audit garbage collection
Jul 27 07:16:35:223 [Thread-112239, mon_config_service] Error when running on timer method in separate thread. Reason is (1) error, Not able to callback for timer cleanupGarbage.1200000. Reason is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [ delete from ssrv2audittrail where id in ( select top(?) st.id from ssrv2audittrail st where st.timestamp < ? order by st.timestamp asc )]; SQL state [HY008]; error code [0]; The query has timed out.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
Feedback
