UIM MCS SSRV2AuditTrail table is over 24gb in size.
Article ID: 106271
Updated On:
Products
Issue/Introduction
The UIM MCS SSRV2AuditTrail table is over 24gb in size. How can it be reduced in size.
Environment
Release:
Component:
Resolution
The SSRV2AuditTrail history can be safely removed.
This script will empty SSRV2AuditTrail and SSRV2AuditTrailModification.
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
