Every now and then you get a helpful alert from one of your database servers about disk space being low (you're monitoring that, right?), or a bunch of autogrowth alerts (you're monitoring that too, right?) - but what happens when you get these for a database that you don't expect growth in? Further, what happens when that database is growing rampantly (say like 1GB/hr in my case) and it's a canned database from a 3rd party product? This time it was the database that SolarWinds uses for collection - and apparently it was collecting a lot of something that it wasn't collecting before.
- you send out an email asking the end users of the system (IT in this case) if anything changed
- adjust so said system stops trying to fill up your drives
Unfortunately, there's often quite a few possibilities for "why" a database is growing - and when it's a canned product, you don't always have the best understanding of why it does what it does when it does it. As a Database Admin, you can help diagnose the problem by letting everyone know what exactly is growing:
First, figure out what table is growing:
CREATE TABLE #Temp_SpSpaceUsed ([name] VARCHAR(255) ,[rows] INT ,[reserved] VARCHAR(255) ,[DATA] VARCHAR(255) ,[index_size] VARCHAR(255) ,[unused] VARCHAR(255) ,[CreatedTime] DATETIME DEFAULT GETDATE()) EXEC sp_msforeachtable 'INSERT INTO #Temp_SpSpaceUsed ([name],[rows],[reserved],[data],[index_size],[unused]) EXEC sp_spaceused [?]' WAITFOR DELAY '00:01:00' EXEC sp_msforeachtable 'INSERT INTO #Temp_SpSpaceUsed ([name],[rows],[reserved],[data],[index_size],[unused]) EXEC sp_spaceused [?]' ;WITH SpSpaceUsed AS ( SELECT [name] ,CAST(LEFT([DATA], LEN([DATA]) - 2) AS INT) AS [DATA] ,[CreatedTime] ,ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY [CreatedTime] ASC) AS [RowNumber] FROM #Temp_SpSpaceUsed ) SELECT a.[name] ,b.DATA - a.DATA AS [GrowthKB] ,(CAST(b.DATA - a.DATA AS FLOAT) / DateDiff(MILLISECOND, a.CreatedTime, b.CreatedTime)) * 1000.0 * 60.0 * 60.0 AS [GrowthKBPerHour] FROM SpSpaceUsed a INNER JOIN SpSpaceUsed b ON a.[name] = b.[name] AND a.RowNumber = 1 AND b.RowNumber = 2 ORDER BY GrowthKBPerHour DESC DROP TABLE #Temp_SpSpaceUsed
Next, look at the culprit (assuming there's one table doing all the damage - I've found that's usually the case) and try to decipher the column names. Finally, run a few queries against the table - WITH(NOLOCK) of course - hey, this table is pretty busy. Now try and see where all the action is coming from. In this case, one of the columns was [IP] so I just did a COUNT(1) by IP for the last hour's records. Sure enough, 99% of the records were coming from a single IP, sent that IP off to the guys who know what IPs mean, and they knew exactly which change caused the growth.
Bottom line - nobody knows everything about everything, but we can learn pretty quick with the right information.