1

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

enter image description here

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) enter image description here

thanks for your help.

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.

  • ohh perfect, that is close to what i need, i am just missing the "change in size MB" field, do you know how can i obtain it? – Eduardo Pivaral Sep 28 '17 at 17:40
  • @EduardoPivaral updated answer. – Erik Darling Sep 28 '17 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.