Skip to content

Reporting on AutoGrowth Events

by on May 28, 2015

We all know that autogrowth events are bad, for a lot of reasons. For starters, it increases the number of VLOGs for the transaction log. Second, depending on your settings, it may also slow down your server if these are often enough. Third, it means that you, as a DBA, missed something in the configuration of the database.

So, how can we see these events ?

Well, there’s Brent Ozar’s sp_Blitz which you can use to check for this and a whole lot of other issues. Kendra Little has a nice article on this, here.

In addition to that, if you fancy creating your own, one can build relatively quickly a mechanism to notify when such an event is detected. Well, not real-time…

Here’s how I did it. You can change the object names, the database names, schema name any way you want. Just make the required changes to the stored procedure as well.

The script I used (the one reading the entries from the default trace) is available for quite a while on various blogs, two of them being Aaron Bertrand’s and Tibor Karaszi. Just keep in mind that the default trace is marked as a deprecated feature in SQL 2014.

I started by creating a table to log these events. Having these events logged may also help in troubleshooting some of the processes that run on your servers. For example, it may show you frequent tempdb autogrowth events. You can check into the active processes and try to figure out what process is causing this and try to address the issue there (is there a tempdb spill, is tempdb used when other methods could be used, etc). Another example would be autogrowth events affecting transaction logs. One, you will have to address the increased number of vlogs and second, you’ll have to identify what is causing that and see if it’s a tlog misconfiguration or a process running a really long transaction which can be changed.

USE [DBA]
GO

IF OBJECT_ID('[monitor].[AutoGrowthEvents]') IS NULL
BEGIN
	CREATE TABLE [monitor].[AutoGrowthEvents]
	(	[server_name] [varchar](128) NULL,
		[event_datetime] [datetime] NULL,
		[event_id] INT,
		[event_name] [varchar](50) NULL,
		[database_id] INT,
		[database_name] [varchar](128) NULL,
		[logical_filename] [varchar](256) NULL,
		[physical_name] [varchar](256) NULL,
		[current_size_mb] [float] NULL,
		[is_percent_growth] [varchar](3) NULL,
		[growth_setting] [float] NULL,
		[growth_size_mb] [float] NULL,
		[event_duration_ms] [float] NULL,
		[reported] BIT DEFAULT(0)
	) ON [DATA]

	CREATE CLUSTERED INDEX [CIX_servername_eventid_databaseid_logicalname_eventdatetime] ON [monitor].[AutoGrowthEvents]
	(	[server_name],
		[event_id],
		[database_id],
		[logical_filename],
		[event_datetime],
		[growth_size_mb]
	)
	WITH (	PAD_INDEX = OFF,
			STATISTICS_NORECOMPUTE = OFF,
			ALLOW_PAGE_LOCKS = ON,
			ALLOW_ROW_LOCKS = ON,
			ONLINE = ON,
			SORT_IN_TEMPDB = ON
	) ON DATA
END

Then, I created a stored procedure that would read the default trace files and look for EventClass’es 92 (Data File Autogrowth) and 93 (Log File Autogrowth). Once the stored procedure was created, I created a job that runs every hour and runs that stored procedure.

USE [DBA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[dba_CheckAutoGrowEvents]
AS
BEGIN
	DECLARE @filename NVARCHAR(1000);
	DECLARE @bc INT;
	DECLARE @ec INT;
	DECLARE @bfn VARCHAR(1000);
	DECLARE @efn VARCHAR(10);
	DECLARE @operator VARCHAR(100); -- email distribution list
	DECLARE @profile	VARCHAR(100)
	DECLARE @ReportHTML  NVARCHAR(MAX);
	DECLARE @Subject NVARCHAR (250);

	DECLARE @FQN	VARCHAR(150)
	DECLARE @Domain VARCHAR(100)

	EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT   
	SELECT @FQN = CAST(SERVERPROPERTY('MachineName') AS VARCHAR) + '.' + @Domain 

	-- Set email distrubution list value
	SET @operator = 'dba@corporatemail.local'
	SET @profile  = 'Email Profile'

	-- Get the name of the current default trace
	SELECT @filename = CAST(value AS NVARCHAR(1000))
	FROM ::fn_trace_getinfo(DEFAULT)
	WHERE traceid = 1 AND property = 2;

	-- rip apart file name into pieces
	SET @filename = REVERSE(@filename);
	SET @bc = CHARINDEX('.',@filename);
	SET @ec = CHARINDEX('_',@filename)+1;
	SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
	SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

	-- set filename without rollover number
	SET @filename = @bfn + @efn

	INSERT INTO [DBA].[monitor].[AutoGrowthEvents]
	SELECT 
		CAST(@FQN AS VARCHAR(128)) AS [server_name],
		CAST(ftg.[StartTime] AS DATETIME) AS [event_datetime], 
		CAST(te.[trace_event_id] AS INT) AS [event_id],
		CAST(te.[name] AS VARCHAR(50)) AS [event_name], 
		CAST(ftg.[DatabaseID] AS INT) AS [database_id],
		CAST(DB_NAME(ftg.[DatabaseID]) AS VARCHAR(128)) AS [database_name], 
		CAST(ftg.[FileName] AS VARCHAR(256)) AS [logical_filename], 
		CAST((SELECT [physical_name] FROM sys.master_files WHERE [name] = ftg.[FileName] AND [database_id] = ftg.[DatabaseID]) AS VARCHAR(256)) AS [physical_name],
		CAST((SELECT ([size] * 8) / 1024. FROM sys.master_files WHERE [name] = ftg.[FileName] AND [database_id] = ftg.[DatabaseID]) AS FLOAT) AS [current_size_mb],
		CAST((SELECT SUBSTRING('No Yes',3 * [is_percent_growth] + 1, 3) FROM sys.master_files WHERE [name] = ftg.[FileName] AND [database_id] = ftg.[DatabaseID]) AS VARCHAR(3)) AS [is_percent_growth],
		CAST((SELECT CASE WHEN [is_percent_growth] = 1 THEN [growth] ELSE ([growth] * 8) / 1024. END FROM sys.master_files WHERE [name] = ftg.[FileName] AND [database_id] = ftg.[DatabaseID]) AS FLOAT) AS [growth_setting],
		CAST((ftg.IntegerData*8)/1024.0 AS FLOAT) AS [growth_size_mb], 
		CAST((ftg.duration/1000) AS FLOAT) AS [event_duration_ms],
		0 AS [reported]
	FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg 
	INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
	LEFT JOIN [DBA].[monitor].[AutoGrowthEvents] age ON age.[server_name] = @FQN
													AND age.[event_id] = ftg.[eventclass] 
													AND age.[database_id] = ftg.[DatabaseID]
													AND age.[logical_filename] = ftg.[FileName]
													AND age.[event_datetime] = ftg.[StartTime]
													AND age.[growth_size_mb] = (ftg.[IntegerData] * 8) / 1024.

	WHERE (		EventClass = 92  -- Date File Auto-grow
			OR	EventClass = 93) -- Log File Auto-grow 
		--AND StartTime > DATEADD(hh,-1,GETDATE()) -- Less than 1 hour ago
		AND (	age.[server_name] IS NULL 
			AND age.[event_id] IS NULL
			AND age.[database_id] IS NULL
			AND age.[logical_filename] IS NULL
			AND age.[event_datetime] IS NULL
			AND age.[growth_size_mb] IS NULL
		)

	IF @@ROWCOUNT > 0
	BEGIN
		/* Any Events Occur were added to the local collection */
		SET @ReportHTML = '<html><body style="font-family:Calibri;font-size:10pt;">' +
		N'<H2>' + N'Auto-grow Events for ' + @FQN + 
		CASE WHEN SERVERPROPERTY('InstanceName') IS NULL 
			THEN ''  
			ELSE N'\' +  CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) 
		END +
		N'</H2>' +
		N'These are the auto-growth events that occured in the last hour for databases mentioned in the table below. If log shipping is enabled for any of the mentioned databases, please check log shipping.' + 
		N'<table border="1" style="font-family:Calibri;font-size:10pt;">' +
		N'<tr><th>Event DateTime</th>' +
		N'<th>Event Name</th>' +
		N'<th>Database Name</th>' +
		N'<th>File Name (path)</th>' +
		N'<th>Growth in MB</th>' +
		N'<th>Duration in MS</th></tr>' +
		CAST((	SELECT 
					td = [event_datetime], '',
					td = [event_name], '',
					td = [database_name], '',
					td = [logical_filename] + '(path=' + [physical_name] + ')', '',
					td = [growth_size_mb], '', 
					td = [event_duration_ms]
				FROM [DBA].[monitor].[AutoGrowthEvents]
				WHERE [reported] = 0
				ORDER BY [event_datetime] 
				FOR XML PATH('tr'), TYPE 
			) AS NVARCHAR(MAX) ) +
		N'</table></body></html>' ;
    
		UPDATE [DBA].[monitor].[AutoGrowthEvents]
		SET [reported] = 1
		WHERE [reported] = 0

		-- Build the subject line with server and instance name
		SET @Subject = 'ALERT: (WARNING) Auto-grow Events in Last hour on ' + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))

		IF @ReportHTML <> ''
		BEGIN
			-- Send email to distribution list.     
			EXEC msdb.dbo.sp_send_dbmail 
				@profile_name = @profile, 
				@recipients=@operator,
				@subject = @Subject,  
				@body = @ReportHTML,
				@body_format = 'HTML'
		END; -- If there are autogrows in the last day
	END
END

The stored procedure, though, does a trick. Let’s say your workload is not always very heavy, you may have some quite(er) times. In those quieter times, it is probably unlikely to have auto-growth events. Let’s say now that your workload increases suddenly over-night and if you run the job too often you may end up sending false alerts on a growth event that was already reported. And here’s where the log table comes handy.

The trick the stored procedure is doing, it compares the data read from the trace file to the data from previous runs. If it finds an auto-growth event that was already captured, it discards it. Any new event, however, is recorded and then an email is sent. The email is formatted as HTML so it looks nice 🙂

Creating a job should not be such big of a deal, right ?

Fine-tuning the process
Once you have the process up and running, you can correlate / check your process against the SSMS built-in report called “Disk Usage” (see the post from Kendra Little I mentioned at the beginning of this article). This report shows the auto-growth events for the selected database. If you see some events there that are not reported by your process, you should run the job more often, it’s very likely that the default trace files have been rolled-over between 2 occurrences of your job.

Enjoy.

PS: Don’t forget to add a process that prunes this table every now and then. 🙂

From → tips

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: