Cool Tips‎ > ‎Backup and Restore‎ > ‎

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

*/

ċ
Get Database AUTOGROWTH events.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments