.

/*
DESCRIPTION:    Queries the default trace to find out last AUTOGROWTH event

The default trace should be enabled by, well, default

Check if it is enabled:  select status, path from sys.traces where is_default = 1

How far back does it report?

The default trace creates up-to 5 files, 20 mb in size. So depending on how busy the server is, it could be 20 mins or days



Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:
 
*/

 
 
Use master
go
 
declare @curr_tracefilename varchar(500) ;  
declare @base_tracefilename varchar(500) ;  
declare @indx int ;
 
select @curr_tracefilename = path from sys.traces where is_default = 1 ;  
 
set @curr_tracefilename = reverse(@curr_tracefilename);
 
select @indx = patindex('%\%', @curr_tracefilename) ;
 
set @curr_tracefilename = reverse(@curr_tracefilename) ;
 
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;  
 
 
select  
 loginname
 , spid
 --,convert(int, EventClass) as EventClass,  
,DatabaseName
,[Filename]
,Duration
, StartTime
, EndTime
, (IntegerData*8.0/1024) as ChangeInSize
, e.name
, RIGHT(@curr_tracefilename,@indx) as curr_tracefilename
 
from ::fn_trace_gettable(@base_tracefilename, default ) a
INNER JOIN sys.trace_events e    ON eventclass = trace_event_id

where  
            e.name = 'Log File Auto Grow'
        OR  e.name = 'Log File Auto Shrink'
        OR  e.name = 'Data File Auto Grow'
        OR  e.name = 'Data File Auto Shrink'
 
order by StartTime desc ;  
GO

.