Products Affected

All products in the ELM Family.

Issue

SQL Database is getting larger than expected.
General Observations

  1. Typically customers find they can fill a database very quickly with Windows security audit records. To see if this is the case in your environment, look at the Database Status and the Database Table Details in the ELM Server At-a-Glance View.
    In the Database Table Details, compare TNTSecurity and TNTEvent record counts. If the TNTSecurity records are the majority of events being recorded, then there are only two options to consider: filter out some of the security records or be prepared to manage all of the data.
  2. In Windows local policies or group policies, turning off object access and process tracking success categories can significantly reduce the amount of data collected in the event logs, thus reducing the amount of data that is collected and put into SQL.

Considerations

  1. Recommend regular SQL backups to reduce the overall size of the ELM Primary database.
  2. If your disk space is critically low, and you don’t have enough disk available to backup the ELM database, then probably the quickest solution is to detach the ELM database, move it to storage, and start a new database.
  3. If your ELM Primary database transaction log file is larger than the data file, and the database is not being backed up, and there’s enough free disk, then start with a database backup. If the database is in simple recovery mode, this should be enough to help shrink the database or ready it for shrinking. If the database is in full recovery mode, then you should also perform a transaction log backup before expecting the database to shrink.
  4. Once backups are successful you should continue by checking your pruning and archive database settings.
  5. Use Exclude Filters to not collect specific events.
  6. Recommend using a Performance Alarm Monitor to alert on the percentage of free disk space for the logical partition where the database resides.
  7. 7.Recommend upgrading to ELM 6.0 in order to take advantage of archiving/deleting in 5000 row increments. Refer to KBA Upgrading from ELM 5.x to ELM 6.0

Procedure

  1. ELM has a built-in database pruning and archiving feature. If you launch an ELM Console then right-click on the ELM Server name near the top of the left-hand pane, you can choose All Tasks | Database Settings.
  2. This will launch the Database Wizard. From here you can specify the location of the ELM Primary and Failover databases, as well as enable and specify the location of an Archive Database. If an Archive Database has been created, then by default all items that are pruned from the Primary Database are moved to the Archive Database for long-term storage, compliance, or forensics.
  3. The next step in the Wizard is Pruning, where you can set retention levels for each type of Event (which would reduce the size of the TNTEvents table) and/or Alerts under the Alerts tab. Performance Data and/or SNMP Data can also be enabled and selected to prune to the archive by selecting the Archive box under the respective tab. On the Schedule tab, select the Scheduled Interval for the pruning and set the Scheduled Hours.

Note: ‘Pruning and Archiving Criteria’ Filters are processed from top to bottom

4.  Retention:
The Retention tab controls the amount of time that events or alerts are kept in the primary ELM database. Records older than the age specified in this window are deleted at the Scheduled Interval and Scheduled Hours selected in the Schedule dialogs.

Retain – Enter the number of periods to keep the data.

Period- Select a period of time. This can be days, weeks, months, quarters, or years. A longer period will cause a larger accumulation of data in the database.

Archive – If Archive is enabled (checked), pruned records will be stored in the Archive Database before deletion from the Primary database. The Archive checkbox is disabled (grayed out) if the archive database has not been configured.

5. Once data has been moved to the Archive database, you may copy that database to other media as needs dictate.

Revision: 1.1

Last Modified:  12/3/2010

Last Reviewed:  12/3/2010

Article Type:  Informational