T-SQL:: Default Trace로 DB 증가량 확인

SQL SERVER 2000 이상

EventClass  : 92, 93, 94, 95 번은 DB File의 증가하거나 Shrink 하는 이벤트 임.
Default Trace가 되고 있는 SQL Server에서는  해당 이벤트를 수집하고 있으며, 증가/축소되는 사이즈를 확인할 수 있다.

begin try  
if (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1 
begin 
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  @@SERVERNAME as server_name
,	    (dense_rank() over (order by StartTime desc))%2 as l1
,       convert(int, EventClass) as EventClass
,       DatabaseName
,       Filename
,       (Duration/1000) as Duration
,       StartTime
,       EndTime
,       (IntegerData*8.0/1024) as ChangeInSize 
from ::fn_trace_gettable( @base_tracefilename, default ) 
where EventClass >=  92      and EventClass <=  95        and ServerName = @@servername 
order by StartTime desc ;   
end     else    
select -1 as l1, 0 as EventClass, 0 DatabaseName, 0 as Filename, 0 as Duration, 0 as StartTime, 0 as EndTime,0 as ChangeInSize 
end try 
begin catch 
select -100 as l1
,       ERROR_NUMBER() as EventClass
,       ERROR_SEVERITY() DatabaseName
,       ERROR_STATE() as Filename
,       ERROR_MESSAGE() as Duration
,       1 as StartTime, 1 as EndTime,1 as ChangeInSize 
end catch