Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Unless they have also been shrunk at some point this should be a simple calculation from initial size to current size based on the auto grow setting? –  Martin Smith Feb 5 '13 at 18:28
    
If it was only modified by autogrow, I would agree but I've seen manual modifications done which may be outside autogrow settings. –  MattyZDBA Feb 5 '13 at 19:03

1 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);
share|improve this answer
    
This is good information, just bear in mind that some of those events could well have expired from the default trace. –  Nic Feb 5 '13 at 23:53
    
Absolutely, but I believe it's the only out-of-the-box monitoring that the OP will get on this with actually doing the proper auditing. –  Thomas Stringer Feb 6 '13 at 0:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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