Our MSSQL 2012 Auditing database is growing by leaps and bounds. It seems easy to prune/archive the audit database, simply by copying data from ADS_EVENT and ADS_EVENT_DETAIL and then deleting it from the actual database.
However, I do not have the same level of comfort with the monitoring data (MOT_TREND_DATA, etc). I have searched many places, but have not seen any document which outlines how to prune/archive/purge Monitoring data from the Audit database. Seems there is an auto-prune by size function for the ‘Derby’ database (Delete older data when the database size grows more than (MB): ), but not for the ‘Use Audit Database’ option.
Thoughts/ideas, as consistently going to the ‘disk well’ for more space will get old, rather quickly.
I was able to get the answer from SAP. It is safe to delete data from MOT_TREND_DATA if it is not necessary for reporting or analysis.
The one challenge I found is that the TS value (timestamp) is not indexed. TIME is indexed, but is in ticks since Unix Epoch. To have a quicker running query you need to delete based on the TIME value.
One thing you might want to look at is what the Monitoring is writing to the database. Do you really need all of your watches to be writing trending data? This would help reduce the need to prune the database.
As I’ve been going through the monitoring configuration, I think there are a lot of the default watch lists that are writing to the trending database that don’t need to be. Almost all of the default ones are configured to write to the trending database. Since these are mainly watch lists on whether the servers are running, it doesn’t make sense to me to write these to the trending database. Shouldn’t we be maintaining a system that is up almost 24/7? That’s my goal anyway. I’ve been turning off the write to the trending database for these. I’ll trend what I really want to look at trending for when I get to that point.
I am also looking the option to turn off the default monitoring in Audit DB as MOT_TREND_DATA table size growing very fast. Do you know where to turn off those default and to just record the stuff we are interested in?
(BOB member since 2009-06-11)