search cancel

How to Truncate Status Message Tables for MS SQL Server

book

Article ID: 42200

calendar_today

Updated On:

Products

DSERIES- SERVER CA Workload Automation DE - System Agent (dSeries)

Issue/Introduction

Regular maintenance dSeries database is required for optimal application performance.  One such maintenance activity is deleting old status messages. 

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. 

Environment

dSeries Server R12.1 and above

Desktop Client 

MS SQL Server

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