Get Database Autogrowth Events

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 22 May 2012

Description

This will return the autogrow events for all databases on the SQL Server instance. It assumes that the default trace is turned on and will only retrun events that occurred within the retention period defined by default trace settings (defaults to a rollover set of 5 files of 20MB each). The number of events measured will also depend on how busy the server is and how many traceable events occurred.

Code

USE Master

GO

DECLARE @filename NVARCHAR(1000);

DECLARE @bc INT;

DECLARE @ec INT;

DECLARE @bfn VARCHAR(1000);

DECLARE @efn VARCHAR(10);

-- 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

-- process all trace files

SELECT

ftg.StartTime

,te.name AS EventName

,DB_NAME(ftg.databaseid) AS DatabaseName

,ftg.Filename

,(ftg.IntegerData*8)/1024.0 AS GrowthMB

,(ftg.duration/1000)AS DurMS

INTO #growth

FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg

INNER JOIN sys.trace_events AS te

ON ftg.EventClass = te.trace_event_id

WHERE (ftg.EventClass = 92 -- Date File Auto-grow

OR ftg.EventClass = 93) -- Log File Auto-grow

ORDER BY ftg.StartTime

SELECT *

FROM #growth

DROP TABLE #Growth

/*

-- to retrieve the default trace settings

USE master

GO

SELECT id, path, max_size, max_files, is_rollover

FROM sys.traces

WHERE id = 1

GO

*/