Article: How to Truncate EdgeSight’s Alert and Alert_Param Table

[NOTE: I had to recently follow similar steps to reduce the size of my EdgeSight database to save disk space]

Document ID: CTX137750   /   Created On: Jul 8, 2013   /   Updated On: Jul 8, 2013

Summary

This document describes how to truncate EdgeSight’s alert and alert_param table. This operation deletes all the “stability” (such as agent’s windows logs like application, system, and security) data.

Requirements

There are many other conditions that will also justify the need to clean out the “stability” data. Following are some guidelines.
If you have an appropriate reason or requirement, skip the “Pre-requisite” section.

Pre-requisite

Required conditions (so you do not arbitrarily empty out the alert and alert_param table)-

  • Core_zbatch_run.log shows [alert] table grooming resulted in timeout (in excess of 240 minutes) error. The ES web console timeout, “Background Services Query” has default setting of 14400 (seconds, 60s/m * 60m/hr * 4hr).
    The setting can be found in “Configure (top tab) > Server Configuration (left tab) > Settings; Timeouts (main window tab).”
  • Many days of failure in core_zbatch_run.log.
  • The filegroup5 is very large, with alert and alert_param tables being excessively large.
  • The customer consent to having the data deleted.
  • And one of the conditions:
  • The database server had run out of disk space or is about to.
  • The “Background Services Query” timeout was extended to a larger value- and timeout failure continues.

Background

Preceding screenshot displays EdgeSight 5.4 Web Server database diagram for “alert” table and its related tables. The highlighted key is “alert_param” table references “alertid,” therefore this relationship must be broken when truncating the “alert” table.

Procedure

As a pre-requisite for the procedure, do this as late in the working day as possible, it will avoid the grooming job and most of the current day’s performance payloads will have been processed.

You must have access to the EdgeSight database. The step described utilizes the SQL Server Management Studio.

Complete the following steps to truncate the EdgeSight’s alert and alert_param table:

  1. Stop the EdgeSight Server’s “Citrix RSSH Admin Service” (it stops the dependent “Citrix RSSH Application Manager” service).

  1. Save a script to recreate the “FK_alert_param_alert” index in “alert_param” table. This script is used to restore the FK.
    Right click on FK_alert_param_alert and select Script Key as > CREATE To > New Query Editor Window.


  1. Drop the FK_alert_param_alert key.
    Right click on FK_alert_param_alert and select Delete.

  1. Click OK on the Delete Object form. (OK button is not displayed in the following screenshot).

  1. Obtain a New Query window. Run both SQL commands here truncate table alert_param and truncate alert.
    In this example, all four commands have been issued at the same time and verified that the tables are empty (0 rows).

  1. Switch back to the Query Window (for example “SQLQuery6.sql…”) to recreate foreign key. Click Execute button to execute the ALTER TABLE commands.

  1. Right click on FK_alert_param_alert and select Refresh.

  1. Expand Keys and verify the FK_alert_param_alert key is present.

  1. Start the EdgeSight Server’s Citrix RSSH Application Manager service (it starts the dependent Citrix RSSH Admin Service service).

More Information

CTX138381 – FAQ – Truncate EdgeSight’s Alert and Alert_Param Table

This document applies to:

Thanks,
Alain

Advertisements