ArcSight DB connectors. tempdb space growing on database

  • KM02113173
  • 29-Jan-2016
  • 29-Jan-2016

Summary

It was found that timebased database connector frequent queries could cause tempdb space to grow on the connected database. This KB provides root cause and a proposed solution.

Error

There is no direct error message on the  Connector Logs.
It was observed by a DBAdmin at a customer site that tempdb space was growing and investigation showed it was the Database Connector from ArcSight that was generating the queries that was causing the tempdb space to grow out of control.

Cause

There could be many reasons for tempdb space on a remote database to grow.  Bad queries, direct queries into tempdb space, joins which query entire tables instead of selective records among others.
Given the queries that we perform from the database connector the most likely cause is an increase in memory use on the database which causes tempdb to be used once a memory threshold has been hit.

That suggestion lead to review of the connector logs showing we were reading the same records over and over again over time.  In this case there were 10 database connections each polling the database every 5 seconds (default).   The timebased query uses the following where at the end of the query.

where alerts.TIME_STAMP >= ? order by alerts.TIME_STAMP

This shows that we will always query events with the same time events until new events are created with a later timestamp.
Hence the reason we could read the same events over and over again.  We would read them but not process them as they would be duplicate.
This is as designed and presents the best scenario to not lose events and duplicates are dropped before normalizing and forwarding to the destination.

default setting is..    agent[x].database[y].frequency=5

 

 

 

Fix

Recommendation was to increase the database frequency value in agent.properties to 30 seconds or higher. 

Customer went with 300 which resolved the issue.

agent[x].database[y].frequency=300