.
/* 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
.