top of page

Script to find SQL Server Data and Log file Auto Growth Event

Writer: Kunal RanpuraKunal Ranpura

--Script to find autogrowth event from the default trace.


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

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

Recent Posts

See All

SQL Server Profiler Trace

--Import multiple trace file in sql Table. --All the rollover files will be automatically imported --provide number tracefile to capture...

Find All Primary Key in SQL Server

select schema_name(tab.schema_id) as [schema_name], pk.[name] as pk_name, substring(column_names, 1, len(column_names)-1) as [columns],...

Comments


bottom of page