Sample Header Ad - 728x90

Looking for help understanding what is happening in this SQL Script that shows key info about SQL Server Autogrowth Events

0 votes
1 answer
71 views
Here is the code below:
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;
I stumbled on this in SQL Shack when my team was experiencing autogrowth events but could not find the cause. This script was exceptionally helpful in tracking down what caused the autogrowths but uses many concepts I have not worked with like the PATH function, ::fn_trace_gettable, and PATINDEX().
Asked by statsGuy (3 rep)
Aug 25, 2021, 04:53 PM
Last activity: Aug 25, 2021, 08:19 PM