Skip to content

Instantly share code, notes, and snippets.

@pregress
Created July 8, 2021 05:54
Show Gist options
  • Save pregress/98c5ae59ecbce72e2b0c462779a9eec3 to your computer and use it in GitHub Desktop.
Save pregress/98c5ae59ecbce72e2b0c462779a9eec3 to your computer and use it in GitHub Desktop.
Scripts to check auto growth events in SQL Server database:
DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
SELECT @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;
SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT @indx = PATINDEX('%\%', @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';
SELECT DatabaseName,
te.name,
Filename,
CONVERT(DECIMAL(10, 3), Duration / 1000000e0) AS TimeTakenSeconds,
StartTime,
EndTime,
(IntegerData * 8.0 / 1024) AS 'ChangeInSize MB',
ApplicationName,
HostName,
LoginName
FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t
INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id
WHERE(trace_event_id >= 92
AND trace_event_id <= 95)
ORDER BY t.StartTime;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment