Server Management - Systems Insight Manager
1745857 Members
4482 Online
108723 Solutions
New Discussion юеВ

HP SIM database LARGE

 
SOLVED
Go to solution
Mike Drobick
Frequent Advisor

HP SIM database LARGE

I read another post stating that the SIM database really should grow beyond 1GB or so. I am only managing about 30-40 devices and my SIM database is 1.7GB. I used SQL Enterprise Manager to run through and I was able to find a table called TrapData that was 1.4GB so I know where the data is. I'm just not sure why. I'm sure I'm doing something wrong, but can someone tell me what processes fill up this table and how can I clean it up?

Thanks.

Mike
11 REPLIES 11
Mike Drobick
Frequent Advisor

Re: HP SIM database LARGE

Sorry...typo. The first sentence should read "shouldn't grow beyond 1GB."

Mike
Joel Rubenstein
Honored Contributor

Re: HP SIM database LARGE

TrapData is used to store the data for incoming traps. By default SIM does not delete incoming events unless you create a task to do so. For every incoming event there is an entry in the notices database table and if the event is a trap then a corresponding entry is made in the TrapData. I suspect you have experienced a trap storm.
Mike Drobick
Frequent Advisor

Re: HP SIM database LARGE

Thanks for the response Joel. Is it safe to clear out the traps knowing there is a duplicate entry in the notices table? Why isn't my notices table 1.4GB as well? Does that get cleaned up somehow?

OK...here is some more data:

notices table
969498 rows
163072KB

TrapData table
964333 rows
1385922KB

trapVarbinData table
964822 rows
277104KB

So, the row count on all of the tables looks consistent, it's just the size of the TrapData table that is way off.

Are there any best practices SIM tasks or SQL processes I should be running to get keep this under control? Is almost 1,000,000 rows too many or normal? My database file is now 2,000,000KB so we're creeping up on the 2GB MSDE limit.

Mike

Re: HP SIM database LARGE

After I installed the v5.0 SP2 I noticed the same thing with my database. I tried to run a maintainence plan on the db and it just hung.

My resolution was to manually back-up the database, de-install insight manager, de-install SQL server. Re-install SQL server, re-install Insight Manager then import the data from the back-up into the new database.

Now, I can run maintainence plans on the database and keep the database files at a manageable level.
Joel Rubenstein
Honored Contributor
Solution

Re: HP SIM database LARGE

1,000,000 events are too many to maintain in the SIM database. You need to create an event collection of "all events older than xxx days" and then create a scheduled delete events task which references that event collection. This will clean up your SIM database.

You also need to investigate the source of the event storm that caused SIM to receive the 1,000,000 traps.
pedro-chicago
Advisor

Re: HP SIM database LARGE

I am a System Admin and not a DBA (but I do have some MSSQL experience), so please forgive me if I say anything incorrect.

I think the size the SIM DB (Insight_v50_0_xxxxxxxxx.mdf and Insight_v50_0_xxxxxxxxx.ldf) will grow to is more a function of how it is configured and managed via MSSQL than how many systems or events are in the DB.

We are currently monitoring 500+ devices (450+ servers and 50+ other) and have almost 60,000 events. Our total DB size is less than 400 MB.

If you are running the HP SIM DB on MSSQL (or any other DB application) you should be doing routine backups of the DB. We currently have a Database Maintenance Plan for the HP SIM DB which does daily Optimizations, Integrity Checks, and Complete Backups of the DB, but no Transaction Log Backups.

Please note that we have the Recovery Model for the DB set to Simple.

It is my understanding that, although the DB still uses the transaction log (*.ldf), the daily full backup of the DB with the Recovery Model set to simple will create a backup file (*.bak) of the DB which includes all data in the DB file (*.mdf) and transaction log file (*.ldf) at the time of the backup.

If you do this, the size of the DB should not grow too large. Depending upon how you may need to recover the DB in the event of corruption, loss of data, or something else, you may need to choose a different Recovery Model. That in turn may affect the size of you DB and Transaction Log files.

I do not believe that any of the HP SIM documentation addresses any of these issues. I had to talk to one of our true DBAs for help getting this set up correctly. I would recommend the same if there is someone that can help you, or do some research on your own on how to manage MSSQL DBs.

I also think it would be helpful for HP to provide some general guidelines on how to manage HP SIM DB, specifically regarding configuration, size, backup, and recovery.

Pete
Mike Drobick
Frequent Advisor

Re: HP SIM database LARGE

You da man Joel. I messed around with the Event Collection customization. I'm not all that impressed, but quickly I was able to come across 100000+ Informational Events from our switch that I didn't even realize got picked up by SIM. The good thing is the 100,000 events are from older than 12/2004, so I've got plenty more events to delete.

Am I missing this, or is there a way to create this event collection so it gives me anything older than 3 months? All I came up with was one that was older than a specific event time.

Thanks again for your help. For those of you scoring at home, don't forget to shrink your SQL database in order to actually recover file space.

Mike
Mike Drobick
Frequent Advisor

Re: HP SIM database LARGE

OK...nevermind...it's late. I found the "older than" option.

Thanks again for all your help.

Mike
Mike Drobick
Frequent Advisor

Re: HP SIM database LARGE

OK...you guys rule!!! Here's the scoop in case you're curious...

996000 Link Up and Link Down events from when we installed the new HP switches a few months ago. I finally found help and turned off those two traps even though I thought I set the switches to not send them. Regardless...the HP SIM database is down from 2GB to 85MB now. Oh, and it took about 5 days to clear out all of the events because I was still fighting off new events while the traps were still on.

I do want to thank you though because I learned a lot about event customization and the clear/delete event functions. And you helped solve my problem!

Mike