How to Truncate Status Message Tables for MS SQL Server
Article ID: 42200
Updated On:
Products
Issue/Introduction
Regular maintenance dSeries database is required for optimal application performance. One such maintenance activity is deleting old status messages.
Environment
dSeries Server R12.1 and above
Desktop Client
MS SQL Server
Cause
dSeries provides DELETESTATUSMESSAGEScli command for cleaning up old messages, which can be scheduled as a dSeries housekeeping job. When this regular maintenance of status messages have been ignored for a prolonged time, server performance can degrade. In such cases, status message tables needs to be truncated manually.
Your resources on the database server and dSeries application server will determine how much data it can process without performance impact. In general 1-2 million rows in the ESP_STATUS_MESSAGE tend show signs of performance degradation.
Resolution
Important Note: CA does not encourage customers to modify the dSeries database directly, however, there are situations where direct maintenance on the database is required. These actions shall be performed by a DBA and in consultation with CA support. Performing this action will permanently remove all entries from the status message tables.
1. Open a support issue and get approval from CA support to perform the steps below.
2. Shutdown dSeries (Primary and Standby)
3. Backup dSeries database
4. Execute the three SQL statements below to obtain the ALTER statements required to drop the Foreign Key constraint.
SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT ' + name FROM sys.foreign_keys WHERE referenced_object_id = object_id('ESP_STATUS_MESSAGE');
SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT ' + name FROM sys.foreign_keys WHERE referenced_object_id = object_id('ESP_SM_SNMP');
SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT ' + name FROM sys.foreign_keys WHERE referenced_object_id = object_id('ESP_SM_LOG');
Execution of the above SQL statements will result in a similar output as below:
ALTER TABLE dbo.[ESP_SM_ACTIVE_APPLS] DROP CONSTRAINT FK__ESP_SM_ACTIV__ID__6DCC4D03
ALTER TABLE dbo.[ESP_SM_AGENT] DROP CONSTRAINT FK__ESP_SM_AGENT__ID__6EC0713C
ALTER TABLE dbo.[ESP_SM_CLIENT_CONNECTION] DROP CONSTRAINT FK__ESP_SM_CLIEN__ID__6FB49575
ALTER TABLE dbo.[ESP_SM_EVENT_AUTHORIZATION] DROP CONSTRAINT FK__ESP_SM_EVENT__ID__70A8B9AE
ALTER TABLE dbo.[ESP_SM_LOGIN_FAILURE] DROP CONSTRAINT FK__ESP_SM_LOGIN__ID__719CDDE7
ALTER TABLE dbo.[ESP_SM_MEMORY_USAGE] DROP CONSTRAINT FK__ESP_SM_MEMOR__ID__72910220
ALTER TABLE dbo.[ESP_SM_SERVER_STATUS] DROP CONSTRAINT FK__ESP_SM_SERVE__ID__73852659
ALTER TABLE dbo.[ESP_SM_SNMP] DROP CONSTRAINT FK__ESP_SM_SNMP__ID__74794A92
ALTER TABLE dbo.[ESP_SM_LICENSE_STATUS] DROP CONSTRAINT FK__ESP_SM_LICEN__ID__756D6ECB
ALTER TABLE dbo.[ESP_SM_LDAP_STATUS] DROP CONSTRAINT FK__ESP_SM_LDAP___ID__7755B73D
ALTER TABLE dbo.[ESP_SM_QUIESCE_STATUS] DROP CONSTRAINT FK__ESP_SM_QUIES__ID__7849DB76
ALTER TABLE dbo.[ESP_SM_LOG] DROP CONSTRAINT FK__ESP_SM_LOG__ID__793DFFAF
ALTER TABLE dbo.[ESP_SM_SNMP_VARIABLE] DROP CONSTRAINT FK__ESP_SM_SNMP___ID__76619304
ALTER TABLE dbo.[ESP_SM_LOG_STACKTRACE_ELEMENT] DROP CONSTRAINT FK__ESP_SM_LOG_S__ID__7A3223E8
5. Execute the ALTER TABLE statements generated from step 4 to drop the Foreign key constraints.
6. Execute the truncate statements below in the order they appear to truncate the tables.
TRUNCATE TABLE DBO.ESP_STATUS_MESSAGE
TRUNCATE TABLE DBO.ESP_SM_ACTIVE_APPLS
TRUNCATE TABLE DBO.ESP_SM_AGENT
TRUNCATE TABLE DBO.ESP_SM_CLIENT_CONNECTION
TRUNCATE TABLE DBO.ESP_SM_EVENT_AUTHORIZATION
TRUNCATE TABLE DBO.ESP_SM_LDAP_STATUS
TRUNCATE TABLE DBO.ESP_SM_LOG
TRUNCATE TABLE DBO.ESP_SM_LOG_STACKTRACE_ELEMENT
TRUNCATE TABLE DBO.ESP_SM_LOGIN_FAILURE
TRUNCATE TABLE DBO.ESP_SM_MEMORY_USAGE
TRUNCATE TABLE DBO.ESP_SM_QUIESCE_STATUS
TRUNCATE TABLE DBO.ESP_SM_SERVER_STATUS
TRUNCATE TABLE DBO.ESP_SM_SNMP
TRUNCATE TABLE DBO.ESP_SM_SNMP_VARIABLE
7. Execute the ALTER TABLE statements below to recreate the Foreign Key constraints
ALTER TABLE ESP_SM_ACTIVE_APPLS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE;
ALTER TABLE ESP_SM_AGENT ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE;
ALTER TABLE ESP_SM_CLIENT_CONNECTION ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE;
ALTER TABLE ESP_SM_EVENT_AUTHORIZATION ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE;
ALTER TABLE ESP_SM_LOGIN_FAILURE ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE;
ALTER TABLE ESP_SM_MEMORY_USAGE ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE;
ALTER TABLE ESP_SM_SERVER_STATUS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE;
ALTER TABLE ESP_SM_SNMP ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE ON DELETE CASCADE;
ALTER TABLE ESP_SM_LICENSE_STATUS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE (ID) ON DELETE CASCADE;
ALTER TABLE ESP_SM_SNMP_VARIABLE ADD FOREIGN KEY (ID) REFERENCES ESP_SM_SNMP ON DELETE CASCADE;
ALTER TABLE ESP_SM_LDAP_STATUS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE (ID) ON DELETE CASCADE;
ALTER TABLE ESP_SM_QUIESCE_STATUS ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE (ID) ON DELETE CASCADE;
ALTER TABLE ESP_SM_LOG ADD FOREIGN KEY (ID) REFERENCES ESP_STATUS_MESSAGE (ID) ON DELETE CASCADE;
ALTER TABLE ESP_SM_LOG_STACKTRACE_ELEMENT ADD FOREIGN KEY (ID) REFERENCES ESP_SM_LOG ON DELETE CASCADE;
Note: The CASCADE may result in the following error in new SQL server version.
Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint 'fk_two' on table 'ESP_STATUS_MESSAGE' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors.
Change CASCADE to NO ACTION
8. Start dSeries
Additional Information
- Refer to CA Workload Automation DE Deployment Best Practices for additional best practices.
Feedback
