BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

[BI 4.x] Pruning Monitoring data from Auditing Database in BI 4.1


 
Search this topic... | Search XI Server Discussion... | Search Box
Register or Login to Post    Forum Index -> Server Administration, Installation, Upgrades -> XI Server Discussion  Previous TopicPrint TopicNext Topic
Author Message
jlgh
Senior Member
Senior Member



Joined: 11 Jun 2009
ASUG Icon
Posts: 56
Location: World Motor Capital (at least it was)


flag
PostPosted: Wed Apr 15, 2015 4:05 pm 
Post subject: Pruning Monitoring data from Auditing Database in BI 4.1

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.
Back to top
jlgh
Senior Member
Senior Member



Joined: 11 Jun 2009
ASUG Icon
Posts: 56
Location: World Motor Capital (at least it was)


flag
PostPosted: Tue Apr 21, 2015 9:16 am 
Post subject: Re: Pruning Monitoring data from Auditing Database in BI 4.1

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.
Back to top
JohnBClark
Forum Enthusiast
Forum Enthusiast



Joined: 27 Mar 2007
ASUG Icon
Posts: 1048
Location: Appleton, WI USA


flag
PostPosted: Wed Apr 22, 2015 8:40 am 
Post subject: Re: Pruning Monitoring data from Auditing Database in BI 4.1

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.

_________________
BusinessObjects BI4.2sp4, SQL Server 2016 (CMS)
Clustered for fail over redundancy
Reporting Tools: Crystal Reports 2016sp4, Web Intelligence
Operating System: Windows 2016 running on VMWare
Reporting databases: SQL Server, Teradata, DB2, Oracle, Hive
Information Steward 4.2sp10
History: BO5, BO6, XIR2, XI3.1, BI4.1.x
Back to top
jlgh
Senior Member
Senior Member



Joined: 11 Jun 2009
ASUG Icon
Posts: 56
Location: World Motor Capital (at least it was)


flag
PostPosted: Thu Apr 23, 2015 8:05 am 
Post subject: Re: Pruning Monitoring data from Auditing Database in BI 4.1

John,
You're entirely right. It's really one of those 'when I have time' things that frankly I'm likely to never have time for!

The life of a part-time sysadmin!
Back to top
taqvisaf
Forum Member
Forum Member



Joined: 12 Aug 2010

Posts: 6



PostPosted: Sat Mar 21, 2020 9:05 am 
Post subject: Re: Pruning Monitoring data from Auditing Database in BI 4.1

Hi John,

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?

Thanks,
Syed
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6678
Location: Connecticut, USA


flag
PostPosted: Mon Mar 23, 2020 7:46 am 
Post subject: Re: Pruning Monitoring data from Auditing Database in BI 4.1

taqvisaf wrote:
Hi John,

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?

Thanks,
Syed

Each watchlist has an option "Write to trending database". If un-checked, history won't be kept.

Joe
Back to top
taqvisaf
Forum Member
Forum Member



Joined: 12 Aug 2010

Posts: 6



PostPosted: Mon Mar 23, 2020 10:02 am 
Post subject: Re: Pruning Monitoring data from Auditing Database in BI 4.1

Got it...Thanks Joe
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Server Administration, Installation, Upgrades -> XI Server Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0292 seconds using 17 queries. (SQL 0.0032 Parse 0.0009 Other 0.0251)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo