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

2 thoughts on “Article: How to Truncate EdgeSight’s Alert and Alert_Param Table”

  1. Hi! I’d like to know how to extract some interesant results about user/application connection times (user logon and logoff and the appname associated…) from the SQL Server EdgeSight tables. I’m a bit confused because i checked that some tables like “ctrx_session_stage” and many others appears with no records.

    1. Manel,

      Your best bet is to explore the Views that are part of the EdgeSight database. This is where I get most of my information. I wish Citrix had published a full schema of the EdgeSight database and you may be able to find some information on the net. For me, I would do a small 5 or 10 row query of each view to see was it stored.

      Thanks for reading,
      Alain

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s