Orion SQL database size

With all the great modules being added to Orion, including Netflow, Voip Monitor, the new APM, and wireless module, my Orion SQL database has swollen to an almost unmanagable size. It just crossed the 100GB mark last week. Its growing out of control. Luckily, I'm running a "robust" stand alone SQL server.

I've talked to support and enabled the routine maintenance items, but nothing seems to 'shrink' the database. I've turned down the length of time I keep stats, I've turned down netflow to the recommended settings, but nothing has worked. The database continues to grow in size.

I'm not an SQL expert. We really need Solarwinds to provide a better maintenance routine to keep the SQL database healthy. Specifically, we need tools built into Orion that will clean-up and compress the SQL database on a scheduled basis. The current tools don't do this.

Does anyone have some solid suggestions for decreasing the SQL database size without risky or complicated SQL procedures.

Find more posts tagged with

orion

sql

truncate

Accepted answers

All comments

Josh_Stephens

Joe,
There are quite a few things we can do to help you here. Can you tell me where the space is being used (which tables and/or logs)?

Josh

joesim123

90% of it is Netflow.

Even when I lowered the time interval to keep data, the database didn't shrink.

I've been noticing my Solarwinds website is getting slower and slower. I'm assuming its the database size that's causing that.

FormerMember

Joe,

How much uncompressed data are you keeping?

Denny

joesim123

Its set like this:

Global Settings

Keep uncompressed data for 1 hours

Keep compressed data for 15 days

bbusbey

Did you apply all the Netflow service packs, this was a problem for us before they issued them.

jbeville

Latest SL2000 Orion, latest Netflow, latest APM, Win2K3, SQL2000 Standard

Db size = 50GB and growing daily

I am having the same issue. looking @ sql manager...the TrapVarbinds table is 28GB. I keep my stats for a longer period. But after maintenance the Db keeps growing. It should be averaging older data, i.e. averagable stats and deleting older log and event files. Correct???

I have had several tickets related. All techs had me update to the latest. Well...each time the Db keeps growing.

I work for a $600 Million company (might not sound like alot...considering all who blog here)...our Oracle ERP, CRM and Accounting db's are not as large as my NPM Database. I cannot even get the IT guys to give me space on our 19TB SAN.

I need help!!!!

joesim123

I started this thread as I was struggling with my huge 132GB Orion DB. I knew the data was netflow related, I just couldn't figure out how to get rid of the old netflows.

Finally, I ran a query on NETFLOWSUMMARY2. I found from the query it had netflow data dating back to May 2007 in it. No wonder my DB was so big. For some reason, all the service packs, compacts, upgrades, etc never cleaned this old netflow data up.

After many trails and errors, and destroying my DB a couple times (I had a backup), I finally decided to clean out the entire netflowsummary2 table.

I ran the "Truncate Table netflowsummary2" on it. It took a hours to run. The end result was a clean DB only about 7gb in size....down from 132GB.

After the truncate finished, I ran a shrink with re-index. This freed up all the free space the truncate left behind.

If your suffering from a huge DB like we were, check the netflowsummary2 table. It might be packed with old data like mine was.

jbeville

When you trunkate a Db table...this should only take as long as it takes to press the Enter Key. THis removes the format from that table, then releases all the data in that table. But that is alot of data to lose. The Db Maintenance and Compact probably took the longest. Mine did.

My problem consisted of Orphaned data in another table TrapVarbinds. I had 117,000,000 records that would have otherwise been unqueriable from the Orion Web Engine.

I would suggest querying for ALL Orphaned data in each large table. Hopefully we have been helpful.

BryanBecker

I'm not a DBA person so some of this stuff I can't follow. It seems different queries, functions, etc. are done within SQL, thru Orion, or something else. Could some post a detailed step-by-step on these processes along with the application used to do each step. For instance the shrink/re-index...not sure where that is. If I'm really going to do this I'd like to do it once.

Thanks!!

jbeville

Not a problem BB. I'm not a DBA here either. Luckily I have DBAs here...but they all work in Oracle. (Close enough) If you are indeed trying to truncate a Db Table...all of this can be done the Orion Database Manager under Database Utilities. When you open this you can open (Dbl click) the NetPerfMon Db. On the right you will see 2 tabs - properties / tables, sort these by column header. This will give you an idea which tables are or seem growing excessively.Best to do this with your polling engines off line. (Stop the services)Right click on the table in question on the tables tab, click on read/write then refresh, then. There is all the data in this Table.You will see a Text field under the Refresh button w/ "Select Top 1000 * From NetFlowSummary2" - Delete this!!Paste the following: "Truncate Table [tablename]", then click Refresh.Get out of DB Manager and run maintenance under the Database Utilities. Once finished, open the Db Manager back up, Click on the NetPerfMon, right click, the Compact Database. This should take a while (Mine did). Close Db manager.Restart pollers and services.

Good luck!!!

BryanBecker

Jay,

Thanks for the info. When I look at the tables it says that 52.4GB is used and that NetFlowSummary2 table is using 46.3 GB of it. My TrapVarbinds table is only 142MB. Do yo suggest truncating both tables?

The total space used is 120GB, Data Space used is 52.4GB, Index space used is 10.4GB.

After the truncation, maintenance, compact do you expect the total space used to shrink? Furthermore will NetFlowSummary2 fill up again? How did you back up the DB before you did all of this? I saw another post talking about deleting the .ldf file, etc.

BB