tim laqua dot com Thoughts and Code from Tim Laqua

Locating Rampant Database Growth

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.

1. you send out an email asking the end users of the system (IT in this case) if anything changed
2. 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:
spSpaceUsed.sql

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.