Disk Usage Report SSMS - query to obtain same info

Question

I would like to have a query that obtains the same info as in the Disk Usage report, section data/Log files autogrowth/autoshrink events

I would like to build a custom monitoring report but i could not find where this report comes. the only thing I could find is that this info comes from:

sys.fn_trace_gettable

but i don't know what field or joins I must do to have the same functionality as the report above.

EDIT: i need these fields, also the size of the growth (for analytical purposes)

thanks for your help.

Answer 1

If you just want to find auto growth and shrink events, you can try this out:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) + N'log.trc'
FROM   sys.traces
WHERE  is_default = 1;

SELECT   te.name AS EventName,
         t.DatabaseName,
         t.DatabaseID,
         t.NTDomainName,
         t.ApplicationName,
         t.LoginName,
         t.SPID,
         t.Duration,
         t.StartTime,
         t.EndTime,
         (IntegerData * 8 / 1024.) as ChangeInSize
FROM     sys.fn_trace_gettable(@path, DEFAULT) AS t
JOIN     sys.trace_events AS te
ON t.EventClass = te.trace_event_id
WHERE    te.name = 'Log File Auto Shrink'
         OR te.name = 'Data File Auto Shrink'
         OR te.name = 'Log File Auto Grow'
         OR te.name = 'Data File Auto Grow'
ORDER BY t.StartTime DESC;

Adapted from Aaron Bertrand's query here.

More reading about querying the default trace here.