SQLServer 维护脚本分享（02）数据库文件增长及收缩情况

--先确定是否开启了默认跟踪,没有则需开启跟踪一段时间
exec sp_configure 'show advanced options',1  
reconfigure  
exec sp_configure 'default trace enabled'

--当前数据库文件历史增长记录
exec sp_executesql @stmt=N'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  (dense_rank() over (order by StartTime desc))%2 as l1
,       convert(int, EventClass) as EventClass
,		case convert(int, EventClass) 
			when 92 then ''Data File Auto Grow''
			when 93 then ''Log File Auto Grow'' 
			when 94 then ''Data File Auto Shrink'' 
			when 95 then ''Log File Auto Shrink'' 
		end as description
,       DatabaseName
,       Filename
,       (Duration/1000) as [Duration(ms)]
,       StartTime
,       EndTime
,       (IntegerData*8.0/1024) as [ChangeInSize(MB)]
from ::fn_trace_gettable( @base_tracefilename, default ) 
where EventClass >=  92      and EventClass <=  95        
and ServerName = @@servername   and DatabaseName = db_name()  
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',@params=N''