Author:   Unknown

Date:       22 May 2012

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.

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