SQL scripts to preview / delete events

Version 3

    Verified Product Versions

    AppSense Management Center 10.0AppSense Management Center 8.7AppSense Management Center 10.1

    Introduction

    As you may know, you can capture events from your managed clients (via the Deployment Agent / CCA) and store them in the Management Server database. There may come a time when you wish to purge some of these events either because you no longer need them or you just wish to claim some of the SQL / disk space back. When you have an excessive amount of events in your database the performance of the Management Console can be impacted.

     

    Detail

    Attached to this document you will find a number of SQL scripts that will allow you to firstly preview and then delete events from the database based on a specified input parameter:

     

    • All high volume events older than - You specify the number of days worth of high volume events you wish to keep. Anything older than this number (in days) will be deleted.
    • All orphaned alerts - Simply removes any alerts that are orphaned (no associated event).
    • All events from deployment group - You specify the deployment group name. All events associated with this deployment group will be deleted.
    • All events older than X days - A more generic script that removes all events older than the number of days that you specify.
    • All events with id - You specify the event ID and all matching events will be deleted.
    • All events within id range - You specify the start and end event ID (inclusive) and all events within this range will be deleted.

     

    All scripts have a default batch size of 10,000 and batch delay of 2 seconds. The batch size can be modified depending on your SQL specifications and requirements. The batch delay has been put in place to mitigate long running exclusive table or index locks.

    Please ensure that you have a SQL database backup prior to running any of the SQL scripts provided

    If you have a particularly large number of events to delete then the above mentioned scripts may take up too much time and resource to complete so you may want to consider another option. Unfortunately it is difficult to define 'a particularly large number of events' because all environments, server specifications and loads are different.

     

    When targetting a large number of event for deletions, we recommend that a job schedule is implemented as part of your database maintetance plan in order to prevent long running scripts from having a further impact to the SQL Server.

    Although batch processing reduces the impact to the SQL transaction log, continuous running removing millions of events, for example, will still generate a large number of transactions as well as impacting on server CPU and the underlying storage I/O.

    If you wish to clear all events immediately, you can request a truncate script from Ivanti Support - please raise a new support case via out Support Portal. A truncate script has the benefit of not causing any overhead to the SQL Server resources and it takes milliseconds to complete.