TrapVarBinds Table is creating havoc once again

I've had issues in the past with the size of my TrapVarBinds Table, I've seen it grow to several Gigs in only a few days. Usually I just truncate the data and all is well again. This morning Orion was completely down as a result of a Disk Space shortage on the SQL server. Our database had grown to 172GB and had used up all our free space on the server. 99% of the data was in the Trap Var Binds Table. After a little tweeking we were able to get into the DB and truncate the TrapVarBinds Table. We had to run the Configution Wiziard again and reboot a few time but the system was restored. I applied SP2 for 9.5 about 2 days ago, so I am assumiung it some how realted. Has anyone else had a simalir issue since upgrading or applying SP2? Does anyone have any suggestions to remedy the TrapVarBinds issue?

Find more posts tagged with

npm_9.5_sp2

nta

trapvarbinds_table

Accepted answers

sean.martinez

You can do the following:

--Delete from Traps where Date time is equal or greater than 1 April 2010

`Delete from Traps Where datetime <= '4/1/2010'`

Once that is completed, you will need to do the Following:

--Delete TrapVarbinds where the Trap ID is not in the Traps Table

`Delete from TrapVarBinds where TrapID not in (select TrapID from Traps)`

I do not recommend removing just the TrapVarBinds Information

I Truncated the TrapVarBinds Table, the only information that will disappear is the Trap Details Column on the Orion Website. The Trap viewer will also not display any Traps removed this way.

All comments

Yann

Hi,

The following should help.

1) How to free up Space?

The below steps will truncate the TrapVarbinds table. If need to keep the data, simply compact the database without truncating the table.

Open Database Manager

Start > All Programs > Solarwinds Orion > Advanced Features > Database Manager

If this is the first time you use the tool, add your database server by clicking on the Add server button.

Expand your database and right-click on the TrapVarbinds table.

Select Query Table.

Type the below SQL query:

 TRUNCATE TABLE TrapVarbinds

and click on Refresh to execute the query.

This may take a while depending how many entries are recorded.

Once done, right-click on the database and select Compact Database.

Compacting a database shrinks it, reindexes it, and removes whitespace.

2) How to keep this table small?

a) Trap Retention Settings

Start > All Programs > Solarwinds Orion > Syslog and SNMP Traps

File > Settings

Modify the "Retain Traps in the database for X days" setting in the general Tab.

b) Ensure Network nodes are not sending useless traps. This is often the case with Windows hosts.

c) Discard Rules -

You can setup a rule to discard the traps you do not want to store.

Setup a Trap Alert/Rule:

http://www.solarwinds.com/NetPerfMon/SolarWinds/wwhelp/wwhimpl/api.htm?href=OrionAdministratorGuide.1.236.htm

Description of the Discard the Trap Action:

Discard the Trap Allows you to delete unwanted traps sent to the SNMP Trap server.

3) Do you need Traps?

Some people do not need Traps but let the Service started.

If you are not using SNMP Traps to monitor your devices, you probably have not configured any rules.

The best solution to avoid the Traps fill in the database is simply to disable the SolarWinds Trap Service.

Run the below commands to stop and then disable the service

net stop SolarWindsTrapService

sc config SolarWindsTrapService start= disabled

Yes there is a space between start= and disabled.

I hope this helps you.

Thanks,

Yann

Ciag

hate to sound like a broken record but ever since we upgraded to v10 I've been experiencing trouble again with my trapvarbinds table . We are send fewer trap to NPM than ever before even less than before v10 upgrade. Yet the varbinds table has grown consistantly since the upgrade. A month prior to upgrading I was tidying up the syslog and traps tables and what we forward to NPM. I have documentation of what the table sizes were before v10.

Yesterday I removed 3 days from the retention period the table size still increased today. So it looks like the maintenance might not be doing it's job on this table.

I'm also experiencing what appear to be deadlocks in the traps and syslog table (not sure if this is in any way related) But I have a ticket open for this already.

My questions are:

If I truncate the varbinds table does it only delete the variable trap info, will I still be able to view the trap messages afterwards?

If not is there a way in which I can selectively deleted older data from the table. Something like a DELETE statement with a WHERE claus. I notice that there are no dates against the data in this table so that makes it harder.

Thanks

MarieB

Hi Ciag--

I'll mark this for the PM to review.

sean.martinez

You can do the following:

--Delete from Traps where Date time is equal or greater than 1 April 2010

`Delete from Traps Where datetime <= '4/1/2010'`

Once that is completed, you will need to do the Following:

--Delete TrapVarbinds where the Trap ID is not in the Traps Table

`Delete from TrapVarBinds where TrapID not in (select TrapID from Traps)`

I do not recommend removing just the TrapVarBinds Information

I Truncated the TrapVarBinds Table, the only information that will disappear is the Trap Details Column on the Orion Website. The Trap viewer will also not display any Traps removed this way.

Ciag

Thanks folks I'll take a look at that

dkobrosly

Hi Sean,

Our Traps table has grown to just about 100 million records. Obviously some work needs to be done on our end to filter out the important versus not so important traps from our various devices.

Using DPA, I did see some crazy wait times for any query involving traps/trapvarbinds, which lead me to this point.

I'm concerned that deleting these records will seriously impact the performance of the tool...Has anyone found themselves in this situation? Any recommendations?

wynvr_dg

Hi sean.martinez,

While trying to figure out why we are adding approximate 100k row per minute to our TrapVarbinds table, I stumbled across this thread. As spursatx mentioned above, my implementation also has some work to do in cleaning up what we are collecting. However, I'm neither a DBA nor a network engineer - so I'm literally learning on the fly here. Hoping you can provide a little info (or at least direction to some)...

Couple of questions:

1. When selecting the top 5000 rows of TrapVarbinds, all of the data looks to be Syslog info. The application owner states he stopped the Syslog collection and a quick check of the Syslog table row count shows a mere crawl to what was being logged before. However, TrapVarBinds grew to over 1mil rows again in no time at all. So, where is all this TrapVarbinds table data coming from?

2. In your response to Ciag above dated Jul 27, 2010 9:46 PM and marked as the correct answer, how did you come up with the 1 April 2010 DateTime? A SW support engineer truncated our TrapVarbinds (250mil + rows), Traps (50mil +) and Syslog (60mil +) tables on a support cal this week related to the DB maintenance running for days on end. After reading your post above, I'm wondering what impact this had on our data now.

I have another question posted about maintenance on DB's in a large implementation and was hoping you might have a little insight on that as well. You can find the post here: DB Maintenance - How to Handle It When Some Tables Have Tens of Millions of Rows?

Thanks for any help you can provide!!

sfp

I know thread is quite old... but still useful, at least for me -- working on "trap problem".

cbreger, Your answer is excellent, but in some circumstances (really huge table as in the first post) I'm not sure if whose queries (especially the second) would finish in any reasonable time limit. So I would expect query to be killed by resource limitations (execution time, RAM, temporary table space,...) and, in really bad case, problems during rollback.

In the case of really huge tables (and hard "NO" for truncate, so for lost of all traps) I would modify scenario to (CAUTION: I didn't tested those queries -- rather description of idea):

1) delete from trapvarbinds where trapid in (select trapid from traps where datetime < 'SOMEDATE')

2) delete from traps where datetime < 'SOMEDATE'

3) SOMEDATE+=delta (time difference for, say 10k or 100k rows)

4) repeat