Technical FAQs

Ask a Question

Is it possible to trim ENM databases such as PLES_AEHistory or PLES_logging ?

Issue
Are there instructions anywhere on how to trim any of ENM databases like PLES_AEHistory, or PLES_Logging ? Similar to how ION_SystemLog or ION_Data can grow and then be trimmed.

Product Line
Event Notification Module (ENM) 8.3.x

Cause
When going to ENM Diagnostics and Monitoring tab, there could be a lot of unneeded errors/events.

Environment
Monitoring and Diagnostics of ENM, trimming record using sql server management studio

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

*********Only Users that are familiar with executing queries in SQL Server Management Studio should perform this task**********

The ENM system event logs are in the [PLES_Logging].[dbo].[Log] table. It can be trimmed by running the following set of queries.

The query below will trim data prior to the UTC time variable, which is set to 2016-11-01 09:54:10. Adjust this time as needed for your system. 
 
  declare @utctime date
  set @utctime = '2016-11-01 09:54:10'

  delete FROM [PLES_Logging].[dbo].[CategoryLog]
  where LogID in (select logid FROM [PLES_Logging].[dbo].[Log]
  where Timestamp < @utctime)

  delete FROM [PLES_Logging].[dbo].[Log]
  where Timestamp < @utctime

The Alarm events can also be trimmed by running the following query:
 
delete FROM [PLES_AEHistory].[dbo].[LG_OPC_ALARM_EVENT]
where EVENT_TIMESTAMP < '2016-11-01 09:54:10'


NOTE: The timestamps are in UTC.

In the event that the PLES_Logging database has grown significantly in size: 

Use the following procedure to clear all of the entries (truncate) in the dbo.Log and dbo.CategoryLog tables. This will allow the DBA or end-user to clear the diagnostics log without the (PLES_Logging) database log file (LDF) growing significantly in size.


Execute the 'dbo.ClearLogs' stored procedure within the 'PLES_Logging' database to clear the diagnostic logs using the SQL query below:

USE [PLES_Logging]
GO

DECLARE    @return_value int
EXEC    @return_value = [dbo].[ClearLogs]
SELECT    'Return Value' = @return_value

GO

**The 'dbo.ClearLog' stored procedure does not clear the alarm events from the database.
 
Was this helpful?
What can we do to improve the information ?