SolarWinds Database Suddenly Spikes In Storage Size, And Rapidly Growing...

I figured I would ask Thwack Nation, while I wait on support. Anyone else out there have this problem before, any advice?

Support keeps wanting to tell me this is all due to record retention, however, I simply cannot wrap my brain around that answer. I am not saying that answer is wrong. I am simply saying it does not make sense to me. Perhaps there is a better example out there that would better assist my acceptance, however, as it stands now, I am simply not buying that answer.

DB server was configured based on SolarWinds best practice pdf, so the current settings, for the most part, should be configured accordingly.

We have not recently added a massive amount of nodes/interfaces/etc.

I know there are many other specific details I have not mentioned here, but I figured I would at least get this posted, and will add more details if needed.

Orion Platform 2015.1.2, NCM 7.4, NPM 11.5.2, DPA 10.0.0, IVIM 2.1.0, SAM 6.2.2

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

Oct 19 2012 13:38:57

Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Network Elements	18398
Nodes	1246
Interfaces	16366
Volumes	786

Alerts	60
Events	128123
Pollers	74173
Polling Engines	3

I have pasted a couple of the descriptions I sent to support, below.

"Case # 950343 Created: Our Orion database has been rapidly increasing in size over the past few weeks."

Message History
Our Orion database has been rapidly increasing in size over the past few weeks. Prior to this issue, our database was growing slowly and steadily, being approximately 65GB-70GB. We had projections of the database reaching 100GB by the end of 2016, or early 2017.

We had a few latency issue, so we rebuilt the SQL server, and swapped them out. A few weeks later, and our database is now over 100GB, which is approximately a full year ahead of schedule.

I have gone through the SQL server settings, as well as the DB settings, and verified the new server matches the old server. I have even gone through and reduced the number of device configs stored in the db, as well as the length of time, which freed up over 5GB of db storage space.

We need to determine why our db is growing so rapidly, and see what we can do to prevent this, or at least slow it down.
Shrinking the DB does absolutely nothing, as there is no space to shrink. Everything there is being used, or at least appears to be, and does not shrink more than a few MB.

Before we rebuilt our SQL server, our DB was approximately 70GB. DB growth was very slow, taking 4-6 months to grow from 60GB to 70GB. We had projected to reach, or be close to reaching, the 100GB size close to the end of this year, or early 2017. Over the past 4 weeks, or so, after rebuilding, and switching over to, our new SQL server, our DB has grown nearly 40 GB. Currently, our DB is approximately 107GB, and growing roughly 100MB every 45-75 minutes.

I have attached a chart/graph showing the severely sharp and rapid increase in the DB size. I am very far from being convinced this is simply due to our data retention. There must be a better, and more believable, explanation.
Thank you,

-Will

DB_growth_Custom_Chart_-_Data_File_Size_201512-201603.png

Thank you,

-Will

Find more posts tagged with

shakkalakka

sql server 2012

rapid database growth

meow

npm 11.5.2

database size

Accepted answers

wluther

rschroeder zackm ecklerwr1 janene16 cahunt RichardLetts

Thank you all for your input on this issue. Here is a rough explanation of my findings.

I went back through the audit trails again, when I got to the office yesterday morning. I DID find a change that occurred 5 days prior to the increased growth, and it was a change in the retention settings.

From what I can put together, the original settings (set many years ago) had been changed/reduced last year.

The most recent change (5 days prior to the growth) increased those retention settings back to what they should have been.

I suppose it slipped passed me the first time because the settings matched what they SHOULD have been all along, so I did not really notice it had recently been changed.

I changed the settings back to what they had been reduced to last year, and then reran the maintenance job.

3/10/2016 8:12:25 AM

Audit Event

User ***** changed settings Detailed Statistics Retention from 60 to 30.

The database size dropped roughly 50% after the maintenance had completed.

I am not sure why those changes took 5 days to show any growth, however, it most definitely caused the damage.

While the error was definitely on my side, I would like to also thank Application Engineer Matthew Lamb (Austin, TX), Support Technician Jamin Walters (Boulder, CO), as well as Jamin's manager, Robert Mandeville (Boulder, CO), for taking the time to look over logs and settings with me. I had already been given an answer, however, they went that little extra bit with me, enough to provide reason, and shine some light in the direction of the root cause. Perhaps DanielleH, maria.bungau, or mrs.alterego can help make sure those guys get their fair share of recognition too, if it's not too much trouble.

Thank you all for the help,

-Will

All comments

rschroeder

I've seen something similar after adding a dozen Cisco WLC 5508's to NCM, and pointing their syslog and traps at the NPM pollers. WLC's send far more information than we anticipated; we had to severely restrict the amount of information those wireless controllers sent to NPM, otherwise we would quickly run out of space. Ultimately we stopped their syslog and trap reporting to NPM and sent the traffic to Splunk, which we sized appropriately for the amount of data that WLC's can send.

Similarly, we were logging too much syslog and trap information from 60+ ASA's to NPM. Again, we turned to Splunk and reduced the amount of info sent to NPM, and as a result our dB growth got back on track.

It might be worth reviewing whether these kinds of devices were changed to send large amounts of syslog or traps to your NPM. Hopefully you'll be able to see which devices may be responsible for the additional growth in space required, and determine if the problem is actual Orion or the devices reporting to it.

wluther

rschroeder We actually had our syslog pointing directly to our main poller, previously. However, as you mentioned, storage can fill up pretty quickly, so we stood up a dedicated Kiwi Syslog server, and repointed everything to send syslog there, instead. (That is the little bump in the graph, towards the middle/end of December.) Since then, we have adjusted our syslog and trap retention settings on our poller to 1 day, just in case, only forwarding the real time change notifications from the Kiwi server to the main poller.

In regards to recently adding devices, we have not had an increase, and not had any abnormal activity, with adding devices. Additionally, while trying to figure this out a couple weeks ago, I adjusted our NCM config storage retention in the db, which reduced that table by nearly 5GB. Unfortunately, that reclaimed space was "refilled" by the following day. I had also increased weekly maintenance jobs to run nightly, which also did not show any changes, neither good nor bad...

Thank you,

-Will

zackm

I would not think this would be an issue with SolarWinds as you have indicated no changes to SolarWinds in this process. The only change has been the SQL server, so I would definitely start there.

Top 25 Tables by Size:

SELECT TOP 25    t.NAME AS 'Table Name',    p.rows AS 'Row Counts',    (SUM(a.total_pages) * 8)/1000 AS 'Total Space (MB)'FROM     sys.tables tINNER JOIN          sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN     sys.allocation_units a ON p.partition_id = a.container_idWHERE     t.NAME NOT LIKE 'dt%'     AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255 GROUP BY     t.Name, p.RowsORDER BY     'Total Space (MB)' DESC

Autogrowth Settings:

SELECTS.[name] AS [Logical Name],S.[file_id] AS [File ID], S.[physical_name] AS [File Name],CAST(CAST(G.name AS VARBINARY(256)) AS sysname) AS [FileGroup_Name],CONVERT (varchar(10),(S.[size]*8)) + ' KB' AS [Size],CASE   WHEN S.[max_size]=-1 THEN 'Unlimited'   ELSE CONVERT(VARCHAR(10),CONVERT(bigint,S.[max_size])*8) +' KB' END AS [Max Size],CASE s.is_percent_growth   WHEN 1 THEN CONVERT(VARCHAR(10),S.growth) +'%'   ELSE Convert(VARCHAR(10),S.growth*8) +' KB' END AS [Growth],Case WHEN S.[type]=0 THEN 'Data Only'  WHEN S.[type]=1 THEN 'Log Only'  WHEN S.[type]=2 THEN 'FILESTREAM Only'  WHEN S.[type]=3 THEN 'Informational purposes Only'  WHEN S.[type]=4 THEN 'Full-text 'END AS [usage],DB_name(S.database_id) AS [Database Name]FROM sys.master_files AS SLEFT JOIN sys.filegroups AS G ON ((S.type = 2 OR S.type = 0) AND (S.drop_lsn IS NULL)) AND (S.data_space_id=G.data_space_id)--Enter your Database name hereWHERE S.[name] LIKE '%SolarWinds%'

Database Recovery Model:

SELECT Name AS [Database Name],Recovery_Model_Desc AS [Recovery Model] FROM sys.Databases--Enter database name hereWHERE Name LIKE '%SolarWinds%'

I would also check your maintenance plans and see what settings/tasks you have enabled for this database.

That's a start anyways, there are a LOT of different reasons your database could be growing like this, but the results of these queries might give a little more insight to point in the right direction.

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

wluther

zackm

I appreciate your time and suggestions. I have run the queries, and provided the results below.

In regards to maintenance plans for this db, I only have the nightly backup set to run.

My brain tells me this is weird (top 25 tables by size), as the problem started alongside February, yet only the last day of February shows up in the list. I think I would have expected to see February show up in that list more than it did...

Top 25 Tables by Size:

Table Name	Row Counts	Total Space (MB)
NCM_ConfigArchive	120562	5055
InterfaceAvailability_Detail	107785381	3752
CPUMultiLoad_Detail	29699100	2087
APM_HardwareItem_Detail	15424555	1809
InterfaceTraffic_Detail_20160110	4670899	919
InterfaceTraffic_Detail_20160113	4684693	918
InterfaceTraffic_Detail_20160109	4665658	917
InterfaceTraffic_Detail_20160106	4671086	917
InterfaceTraffic_Detail_20160107	4651588	916
InterfaceTraffic_Detail_20160111	4670449	916
InterfaceTraffic_Detail_20160112	4654713	915
InterfaceTraffic_Detail_20160308	3929046	909
InterfaceTraffic_Detail_20160108	4648887	897
InterfaceTraffic_Detail_20160114	4513393	888
InterfaceTraffic_Detail_20160105	4516877	871
InterfaceTraffic_Detail_20160104	4386311	863
InterfaceTraffic_Detail_20160103	4385628	862
InterfaceTraffic_Detail_20160101	4355024	859
InterfaceTraffic_Detail_20151230	4344285	857
InterfaceTraffic_Detail_20151228	4346332	854
InterfaceTraffic_Detail_20151231	4361517	854
InterfaceTraffic_Detail_20160102	4374802	845
InterfaceTraffic_Detail_20151229	4347805	842
InterfaceTraffic_Detail_20160301	4093320	802
InterfaceTraffic_Detail_20160229	4098555	802

Autogrowth Settings:

Logical Name	File ID	File Name	FileGroup_Name	Size	Max Size	Growth	usage	Database Name
SWNPMDB	1	E:\MainData\SWNPMDB.mdf	PRIMARY	112448960 KB	Unlimited	102400 KB	Data Only	SWNPMDB
SWNPMDB_log	2	F:\MainLog\SWNPMDB_log.ldf	NULL	5632832 KB	41059328 KB	512000 KB	Log Only	SWNPMDB
SWNPMDB_FG1	3	E:\MainData\SWNPMDB_FG1.mdf	FG1	102400 KB	Unlimited	102400 KB	Data Only	SWNPMDB
SWNPMDB_FG2	4	E:\MainData\SWNPMDB_FG2.mdf	FG2	102400 KB	Unlimited	51200 KB	Data Only	SWNPMDB
SWNPMDB_FG3	5	E:\MainData\SWNPMDB_FG3.mdf	FG3	102400 KB	Unlimited	51200 KB	Data Only	SWNPMDB
SWNPMDB_FG4	6	E:\MainData\SWNPMDB_FG4.mdf	FG4	51200 KB	Unlimited	51200 KB	Data Only	SWNPMDB

Database Recovery Model:

Database Name	Recovery Model
SWNPMDB	SIMPLE

Thank you,

-Will

janene16

It looks like even though your DB is set to Simple recovery, your LDF file size is huge. I wonder if there's a pending transaction still queued..

wluther

janene16 5GB is huge... lol... We had been fighting that stupid log file for a while too... it had blown up to upwards of 50GB+, and I am not really sure how/why it decided to calm down. I have had multiple tickets open on that issue too, but, like I said, it just kind of calmed down on its own, and support called it a day because it stopped growing.

I have had the DPA people look into it, as well as multiple NPM and SAM support techs too. Nothing ever seemed to work, with the exception of the dark magic used by the server gremlins while nobody was looking, then it stopped.

wluther

zackm Well, I just looked up the change management records for that server swap. As it turns out, it was initially scheduled for Feb 1st, however, was rescheduled, and the work actually took place on Feb 11th, roughly 12 days after the growth accelerated.

I kind of wish I didn't know that, because now I am even more confused, as that server swap was the only "unusual" work/changes done to our SolarWinds environment.

-Will

patriot

Very nice report Zackm. Does the first query have a variable for the target database? Am I not seeing it?

zackm

you could put something like this at the top:

USE SolarWindsDatabase

But no, I did not specify it distinctly. Generally I always have the correct DB selected in SSMS before running queries so I just don't normally add that line (even though I should)

Nice catch.

zackm

x2 on the LDF file, that is excessive from what I am used to seeing.

Even though it wouldn't technically be supported by SWI, I generally like to put my DBs into FULL recovery and create a more robust backup schema that would allow for a better recovery timeframe if there were problems. Another benefit of this model is that you get to control your log file size more grnularly by scheduling Transaction Log backups.

This is not something to take over lightly though and should be discussed with your DBAs.

The other thing that immediately strikes me is that your Interface Details tables are about twice the size I would have suspected.

Given the default 9 minute polling interval, each interface should see 160 polls/day.

Your original screenshot shows 16,366 interfaces, which would mean you should see ~2,618,560 rows on each of those tables.

Same thing with the InterfaceAvailability_Detail table. Default polling is 2 minutes, you should have 82,484,640 rows to cover 7 days of detail data (default detail retention).

If you have not purposefully increased the amount of polls hitting those interfaces, this should be investigated.

It sounds like you have increased retention dates in your environment based on your original post. And that's fine. But it is something to note on why those tables are so large.

What I would recommend is using SAM to setup some SQL Server User Experience monitors on your database and try and trend the top 10 tables for a week or two and see where the problems are more granularly.

If you don't want to use SAM, just run that top 25 query every day at about the same time and copy the results out to excel and then see where the largest growth is really happening.

It's really not directly obvious by this data, I wouldn't even blame this on the log file, that's just a red flag. But if you can trend some data over time at a granular, table-level, that should point you to where the actual growth is instead of just seeing that the whole DB is growing...

RichardLetts

Are you sure your database is actually growing in size? Yes, I know the files are growing in size, but that is not really an issue since recently accessed data is cached..

I note your moved to a new SQLserver database -- are the database server setting the same on both of them? e.g. autoshrink, etc?

in my environment the size of the database files is ~500GB, but the space actually consumed by the database tables is ~344GB

note.. if you have wireless of any size then you quickly end up with LOTS of wireless data:

(my three largest tables)

Wireless_Interfaces_DAILY	294004748	89690
Wireless_Clients_DAILY	134176157	60397
Wireless_Clients_SessionHistory	53717277	28526

ecklerwr1

That then starts to point to it not being the SQL server swap itsself then if it had already started it's exponential growth over a week before the server was swapped. Zackm seems to be onto something though run those queries every day for a few days and see if anything big jumps out maybe... I've also recently finally figured out the correct permissions needed to get AppInsightforSQL working and I'm running it now on my main SQL Server holding the solarwinds tables. That AppInsightforSQL show an amazing amount of data about SQL server. alterego helped me with what I needed with this: Configuring and Integrating AppInsight for SQL

This has been great to finally see what's going on deep inside my SQL Server day to day and spot trends. It was also great to setup a service account with only the required permissions needed by AppInsightforSQL and not needing a sa or domain admin account with full permissions to entire SQL Server ie. it made my dba much happier to go along with versus saying oh I need an sa account on the SQL Server you manage.

cahunt

RichardLetts is correct about that -

Wireless takes up a lot... seeing your APM in the top 5 makes me curious if there have been any increased Hardware Health monitoring?

and for god sakes man, limit your log file size!

j/k - cause your DB is still far smaller than mine....I poll for too many tables.

wluther

rschroeder zackm ecklerwr1 janene16 cahunt RichardLetts

Thank you all for your input on this issue. Here is a rough explanation of my findings.

From what I can put together, the original settings (set many years ago) had been changed/reduced last year.

The most recent change (5 days prior to the growth) increased those retention settings back to what they should have been.

I suppose it slipped passed me the first time because the settings matched what they SHOULD have been all along, so I did not really notice it had recently been changed.

I changed the settings back to what they had been reduced to last year, and then reran the maintenance job.

3/10/2016 8:12:25 AM

Audit Event

User ***** changed settings Detailed Statistics Retention from 60 to 30.

The database size dropped roughly 50% after the maintenance had completed.

I am not sure why those changes took 5 days to show any growth, however, it most definitely caused the damage.

Thank you all for the help,

-Will

ecklerwr1

I'm really glad you got to the bottom of this wluther!

danielleh

Glad you got this resolved, too! I'll pass the message along, I'm sure they'll appreciate the S/O!