File Auto Grow history using default trace

Why it is important?

Unpredictable "File Auto Grow" can be real bottleneck in your highly performing system.
At first, if file growing chunks are too small the create unnecessary fragmentation on you physical drive and for the log file it generates humongous number of VLFs. The fragmentation slows down all your I/O operations and huge number of VLFs can really slow down your server startup procedure.
At second, when your chunks are too big it might generate unpredictable few seconds delay during busy business hours.
So, tracking these events and fight with them is one of DBA's priorities.

Last month I had a post on how to track and possibly alert a DBA about file growth event (https://slavasql.blogspot.com/2019/07/capturing-filegrowth-event-using-job.html)

That is pretty helpful, but what if you can't set it up or want to know about the past file growth events.

In that case a DBA might use a limited option to look back in the SQL Server history.

Assuming your default trace is enabled and nobody deleted its log files.
Then you will be able to run the following script to figure out ALL historical Auto file growth events:

DECLARE @FileName NVARCHAR(MAX) = (        SELECT CAST(value as NVARCHAR(MAX))        FROM ::fn_trace_getinfo(DEFAULT)        WHERE traceid = 1 AND property = 2); SELECT @FileName = LEFT(@FileName        , LEN(@FileName)-CHARINDEX('_'        ,REVERSE(@FileName),1))               + RIGHT(@FileName,4); SELECT t.StartTime        , [Event Name] = te.name        , t.DatabaseName         , t.Filename        , [Growth, Mb] = CAST(t.IntegerData/128. as DECIMAL(18,3))        , [Duration, ms] = t.duration/1000        , t.Hostname        , t.LoginName        , t.ApplicationName FROM ::fn_trace_gettable(@FileName, DEFAULT) as t INNER JOIN sys.trace_events as te        ON t.EventClass = te.trace_event_id WHERE EventClass in (92,93) ORDER BY t.StartTime DESC; GO

Running this you are supposed to get similar output:

Limitations:

As you can guess, nothing is given for free. Default trace is capturing only "Log File Auto Grow" and
"Data File Auto Grow" (besides of many others), but does not capturing "Manual File Size" changes. Also, you can see only the increase in size, but not original or new file size. However, for simple troubleshooting that has to be more than enough.