File Growth Analysis via Default Trace

SQL Server continuously tracks certain critical events in the “default trace”. Among those events are the file growth events triggered when SQL Server automatically grows a database data or log file, or when a user manually expands a file.

The code below shows a simple method of seeing file growth events contained in the default trace.

DECLARE @trcfilename nvarchar(260);
DECLARE @MinEventSequence int;
DECLARE @MaxEventSequence int;

SELECT @trcfilename = path 
FROM sys.traces 
WHERE is_default = 1;

IF OBJECT_ID('tempdb..#trctemp') IS NOT NULL
BEGIN
    DROP TABLE #trctemp;
END

SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt

/*
	Specify a time-range for our query.  Below example
	will only show events that are at most 60 minutes
	old.
*/
SELECT @MinEventSequence = MAX(t.EventSequence) 
FROM #trctemp t 
WHERE t.StartTime < = DATEADD(MINUTE, -60, GETDATE());

SELECT @MaxEventSequence = MIN(t.EventSequence) 
FROM #trctemp t 
WHERE t.StartTime >= DATEADD(MINUTE, 0, GETDATE());

IF @MaxEventSequence IS NULL
SELECT @MaxEventSequence = MAX(t.EventSequence)
FROM #trctemp t;

SELECT tt.EventSequence
	, [Database] = d.name
    , [Event] = te.name
	, tt.HostName
	, tt.ApplicationName
	, tt.LoginName
	, DurationInSeconds = CONVERT(decimal(10,3), tt.Duration / 1000000e0)
	, tt.StartTime
	, tt.EndTime
	, tt.ServerName
	, tt.FileName
FROM #trctemp tt
    INNER JOIN master.sys.databases d ON tt.databaseid = d.database_id
    INNER JOIN sys.trace_events te ON tt.EventClass = te.trace_event_id
WHERE       tt.EventSequence >= @MinEventSequence
    AND tt.EventSequence < = @MaxEventSequence
    AND EventClass IN 
		(
			  92	--Data File Auto Grow
			, 93	--Log File Auto Grow
			, 94	--Data File Auto Shrink
			, 95	--Log File Auto Shrink
		)
ORDER BY tt.EventSequence;

To test this code, I created a simple, small database, and populated it with some data. Here’s the code to do that, which will drop any database you happen to have named ‘TestDB’:

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON (NAME = TestDB_system, FILENAME = 'C:\TEMP\TestDB_system.mdf'
    , SIZE = 4MB, MAXSIZE = 100MB, FILEGROWTH = 10MB)
LOG ON (NAME = TestDB_log, FILENAME = 'C:\TEMP\TestDB_log.ldf'
    , SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 10MB);
GO
USE TestDB;
GO
CREATE TABLE dbo.TestTable
(
	TestTableID int NOT NULL
		CONSTRAINT PK_TestTable
		PRIMARY KEY CLUSTERED
		IDENTITY(1,1)
	, SomeVal NVARCHAR(1000) NOT NULL
);

/*
    This will insert 10,000 rows into the TestTable, enough for several growth events.
*/
;WITH c AS (
	SELECT v1.Val
	FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (8)) v1(Val)
)
INSERT INTO dbo.TestTable (SomeVal)
SELECT CONVERT(nvarchar(1000), CRYPT_GEN_RANDOM(1000))
FROM c C1
	CROSS JOIN c C2
	CROSS JOIN c C3
	CROSS JOIN c C4;

After creating the test database, and growing it by inserting the test data, I ran the analysis code from the top of this post, which shows the following output:

File Growth Event Results

As you can see in the image above, the file growth events for this database, and it’s log files, are clearly outlined. There are four file growth events, each taking less than a second to complete; two for the data files, and two for the log files.

Let me know if you have any questions about this code in the comments below!