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
*/ |
 Updating...
Andy Hughes, Jun 7, 2012, 10:54 AM
|