Many times I see people that are surprised by finding out that their drives are suddenly full but they just aren’t sure which file grew and when. SQL server itself does not track this information. Ideally we would setup a sql agent job to log job growth to a table we could then easily write queries around. However most servers I come across do not have this already setup.
So how can we look into the past and try to figure out what happened? Luckily the default trace keeps track of autogrowth events. By querying the default trace we can look back over the period of time that the trace covers and then compare that information with the current size. The following script will show the amount of growth in the previous 24 hours as well as the percent of change in the file size.
declare @curr_tracefilename varchar(500), @base_tracefilename varchar(500), @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' ;
WITH AutoGrow_CTE (databaseid, filename, ChangeInSize)
AS
(
select databaseid, filename, SUM(IntegerData) as ChangeInSize
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass = 92 OR EventClass = 93
AND DATEDIFF(hh,StartTime,GETDATE())<24
group by databaseid, filename
)
select DB_NAME(database_id) DatabaseName, mf.name LogicalName,
mf.size-ISNULL(ag.ChangeInSize,0) PreviousSize,
ISNULL(ag.ChangeInSize,0) AutoGrowSize,
mf.size CurrentSize,
CAST(ISNULL(ag.ChangeInSize,0)/(mf.size-ISNULL(ag.ChangeInSize,0))*100 AS VARCHAR)+'%' PercentChange
from sys.master_files mf
left outer join AutoGrow_CTE ag
on mf.database_id=ag.databaseid
and mf.name=ag.filename
--WHERE ISNULL(ag.ChangeInSize,0)/(mf.size-ISNULL(ag.ChangeInSize,0))*100 >20
ORDER BY DatabaseName, LogicalName
The commented out line will restrict results to only those above a certain percentage that you specify. Of course you can further tweak this to provide whatever reporting you require or trends that you are interested in.
-Jeremy
