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. 🙂
SQL-PLAY 