Identify File Growth Events

Question

I'm discovering a number of DBs in my portfolio that have been created with default autogrow settings (either 1 MB or 10% increments) that have expanded for an extended period of time. If I wanted to get a gauge of the amount of external fragmentation for each of the DB files, can I get from metadata how many times a DB file has been modified in size (either by autogrow or manually)? To clarify, can I get from metadata the history of DB file modifications for the life of the DB, not just since instance restart?

Answer 1

You can get autogrowth events information from the default trace if it is enabled:

select distinct
    ei.eventid,
    e.name
from sys.fn_trace_geteventinfo(1) ei
inner join sys.trace_events e
on e.trace_event_id = ei.eventid
where name like '%grow%';

You can see from this that the default trace does have the Data File Auto Grow and Log File Auto Grow events capture. To see if you have the default trace enabled on that instance, you can do the following:

exec sp_configure 'default trace enabled';
go

Note: this is an advanced configuration option, so show advanced options would have to get set to 1 to view this configuration option through sp_configure. Also, these two events will not be triggered if the file grew manually.

Here is a quick sample query to get these events:

select
    te.name as event_name,
    tr.DatabaseName,
    tr.FileName,
    tr.StartTime,
    tr.EndTime
from sys.fn_trace_gettable('<Trace Path>', 0) tr
inner join sys.trace_events te
on tr.EventClass = te.trace_event_id
where tr.EventClass in (92, 93)
order by EndTime;

And you can get <Trace Path> from the system function sys.fn_trace_getinfo:

select *
from sys.fn_trace_getinfo(1);