Blog

One way to keep track of your data- and log file sizes in SQL Server, is to send an email notification whenever a file growth occurs.

If you can learn at which times file growth occurs, you may be able to dig out the root cause (e.g. long running queries) for file expansions. Note that file expansions are not necessarily a problem however. The cause may just be normal linear growth. But if you suspect that some process(es) is responsible for a large part of the growth, the method described here may be useful.

The solution uses the Default Trace in SQL Server to list the latest file growth events. When such events are detected, an email with the new events since last email is sent. The mail uses a HTML table in the mail body.

I suggest running the job every 10 minutes from SQL Agent. Create a job step with the following T-SQL. You’ll need to change the parameter settings on the msdb.dbo.sp_send_dbmail procedure.

IF (SELECT OBJECT_ID('tempdb..#TLind_LOG_GROWTH_EVENTS')) IS NULL BEGIN
    CREATE TABLE #TLind_LOG_GROWTH_EVENTS
        (
			DatabaseName NVARCHAR(128) NOT NULL
			,FileName NVARCHAR(256) NOT NULL
			,HostName NVARCHAR(256) NULL
			,ApplicationName NVARCHAR(256) NULL
			,LoginName NVARCHAR(256) NULL
			,StartTime DATETIME NULL
			,EndTime DATETIME NULL
			,ChangeSize NUMERIC(10,2) NULL
			,EmailSent DATETIME NULL
        )

	CREATE UNIQUE CLUSTERED INDEX TLind_LOG_GROWTH_EVENTS_CLU ON #TLind_LOG_GROWTH_EVENTS (DatabaseName,StartTime DESC, EndTime)
	CREATE INDEX TLind_LOG_GROWTH_EVENTS_EmailSent_IDX ON #TLind_LOG_GROWTH_EVENTS (EmailSent)
END

INSERT #TLind_LOG_GROWTH_EVENTS
SELECT DISTINCT DB_NAME(DatabaseID) AS DatabaseName
	,[FileName]
	,HostName
	,ApplicationName
	,LoginName
	,StartTime
	,EndTime
	,CAST((IntegerData*8.0/1024) AS NUMERIC(10,2)) AS ChangeSize
	,NULL AS EmailSent
FROM
    ::fn_trace_gettable((SELECT LEFT([path], LEN([path]) - PATINDEX('%\%', REVERSE([path]))) + '\log.trc' FROM sys.traces WHERE is_default = 1), DEFAULT) AS TRC
WHERE
	TRC.EventClass IN (92,93)
AND
	NOT EXISTS
		(
			SELECT *
			FROM #TLind_LOG_GROWTH_EVENTS PREV_TRC
			WHERE PREV_TRC.DatabaseName = TRC.DatabaseName
			AND PREV_TRC.StartTime >= TRC.StartTime
		)

IF @@ROWCOUNT > 0 BEGIN
	DECLARE @MAIL_BODY VARCHAR(MAX)
 
	/* HEADER */
	SET @MAIL_BODY = '<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:consolas;text-align:center;">' +
		'<tr>
		<th>DatabaseName</th>
		<th>FileName</th>
		<th>HostName</th>
		<th>ApplicationName</th>
		<th>LoginName</th>
		<th>StartTime</th>
		<th>EndTime</th>
		<th>ChangeSizeMB</th>
		</tr>'
 
	/* ROWS */
	SELECT
		@MAIL_BODY = @MAIL_BODY +
			'<tr>' +
			'<td>' + DatabaseName + '</td>' +
			'<td>' + FileName + '</td>' +
			'<td>' + ISNULL(HostName, '...') + '</td>' +
			'<td>' + ISNULL(ApplicationName, '...') + '</td>' +
			'<td>' + ISNULL(LoginName, '...') + '</td>' +
			'<td>' + ISNULL(CONVERT(VARCHAR(19), StartTime, 121), '...') + '</td>' +
			'<td>' + ISNULL(CONVERT(VARCHAR(19), EndTime, 121), '...') + '</td>' +
			'<td>' + ISNULL(CAST(ChangeSize AS VARCHAR(12)), '...') + '</td>' +
			'</tr>'
	FROM
		#TLind_LOG_GROWTH_EVENTS
	WHERE
		EmailSent IS NULL
 
	SELECT @MAIL_BODY = @MAIL_BODY + '</table>'

	SELECT @MAIL_BODY
 
	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = '<YouEmailProfile>',
		@recipients = '<YourEmailRecipients>',
		@subject = 'Log Growth Events on <YourServer> (Table is tempdb..#TLind_LOG_GROWTH_EVENTS. SQL Agent job is <YourSQLAgentJobName>',
		@body = @MAIL_BODY,
		@body_format='HTML'

	UPDATE #TLind_LOG_GROWTH_EVENTS SET EmailSent = GETDATE() WHERE EmailSent IS NULL
END

The email will look something like this example:

To create file growth events in order to test the solution, run the following script. Preferably on a test server of course.

CREATE DATABASE LogFileAutoGrow
GO

USE LogFileAutoGrow
GO

CREATE TABLE SomeTable (SomeColumn CHAR(8000))
GO

INSERT SOMETABLE
SELECT TOP 2000 CAST('' AS CHAR(8000)) AS SomeColumn
FROM SYS.OBJECTS S1
	CROSS JOIN SYS.OBJECTS S2
	CROSS JOIN SYS.OBJECTS S3