How to shrink internal SES Log tables - Hist_Event_Log, Event_Log, Buffers_Log, Hist_Buffers_Log
Issue:
SES Log tables can grow, and SES Administrators require a means of truncating or clearing out obsolete information.
SES tracks information about activity within the SES-managed environment.
Since all customer organizations have different rules about how long audit information should be retained, there are no specific settings in SES to determine at what point such logs should be truncated.
The files that can potentially grow to substantial sizes (the pace of growth will largely be determined by the size of the SES implementation) are:
• Hist_Event_Log
• Event_Log
• Buffers_Log
• Hist_Buffers_Log
Solution:
There are three Stored Procedures in the SES Database that a customer's DBA can run that will clear out these log files.
spClear_Commands
This procedure accepts no arguments/parameters. It automatically clears out of the Commands history all commands whose expiry date is older than the limit for retention of Commands to be stored. This is a setting that can be defined in the SES Console (see SES User Guide for more information on setting this).
spClear_HistoryEventLog
This procedure accepts 2 arguments/parameters. It automatically backs up (to the specified file) and deletes from the History Event Log all log items that are older than the date specified in the first argument. Having completed that, it will then clear out associated information relating to the purged records from both the Buffers_Log and Hist_Buffers_Log tables.
• @ClearDate - in datetime format - this indicates the point in time prior to which current audit log can be rolled over to audit history. NOTE: If this argument is not passed, the stored procedure will default to a point in time 90 days prior to today's date.
• @LogFileName - is the file location where the backed up data from the above process is to be stored. NOTE: If this argument is not passed, then no backup will be done of the data to be purged.
NOTE: The SQL account used must have privileges to run xp_CmdShell from within SQL in order to create the backup file.
spClear_EventLogs
This procedure accepts no arguments/parameters. It rolls over into the History Event Log table any events in the (current) Event Log whose Data Expired setting (a column in the record, whose date is defined by settings within SES that relate to when current events should be rolled over to history). See SES User Guide for more information on setting this.