报告当前服务器上运行的trace

与大家共享一些我写的SQL Server的存储过程，作为一个DBA，希望与大家在数据库领域作一些探讨与交流。

 

if exists (select * from dbo.sysobjects where xtype='P' and name ='up_AutoTrace_report')
drop procedure up_AutoTrace_report
go

create procedure dbo.up_AutoTrace_report
@traceid int =0, --默认全部
@message varchar(8000) output
as
/**********
--文件名 :
--用途 :报告当前服务器上运行的trace
--输入参数 :
--返回值解释 :
--创建者 : summer.yang
--创建日期 : 2005-6-18
--修改者 :
--修改日期 :
--修改注解 :
--备注说明 : (引用请保留此信息)
**********/
set nocount on
declare @return int
set @return=0
set @message=''
--------------
--如果不存在trace则退出
if Not exists(SELECT * FROM :: fn_trace_getinfo(0) )
begin
set @message='There is not running any Trace in the server.'
goto finish
end
--------------

if @traceid=0
begin
declare @cursor cursor
set @cursor=cursor forward_only static for
select distinct traceid from :: fn_trace_getinfo(0)
open @cursor
fetch next from @cursor into @traceid
while @@fetch_status=0
begin
select @message =@message+'The opening Trace ID is '+ltrim(str(@traceid))+'.'+char(10)
+'Its details are :'+char(10)
+' The options is '+ convert( varchar(256),value) +char(10)
from :: fn_trace_getinfo(@traceid) where property=1
select @message =@message+isnull(' The file name is "'+ convert( varchar(256),value) +'.trc"',' The file does not exists.') +char(10)
from :: fn_trace_getinfo(@traceid) where property=2
select @message =@message+' The max file size is '+ convert( varchar(256),value)+'MB' +char(10)
from :: fn_trace_getinfo(@traceid) where property=3
select @message =@message+isnull(' The stop time is "'+ convert(varchar(256),value,120)+'"',' There is no stop time.')+char(10)
from :: fn_trace_getinfo(@traceid) where property=4
select @message =@message+' The current status is '+ case convert(int,value) when 1 then 'RUNNING' when 2 then 'CLOSED' when 0 then 'STOPPED' end +'.'+char(10)
from :: fn_trace_getinfo(@traceid) where property=5
set @message=@message+char(10)+' '+char(10)

set @message=@message+'**********************'+char(10)+' '+char(10)

fetch next from @cursor into @traceid
end

end
else
begin 0">--@traceid<>0
if exists (SELECT * FROM :: fn_trace_getinfo(@traceid))
0">begin--@traceid<>0
select @message =@message+'The opening Trace ID is '+ltrim(str(@traceid))+'.'+char(10)
+'Its details are :'+char(10)
+' The options is '+ convert( varchar(256),value) +char(10)
from :: fn_trace_getinfo(@traceid) where property=1
select @message =@message+isnull(' The file name is "'+ convert( varchar(256),value) +'.trc"',' The file does not exists.') +char(10)
from :: fn_trace_getinfo(@traceid) where property=2
select @message =@message+' The max file size is '+ convert( varchar(256),value)+'MB' +char(10)
from :: fn_trace_getinfo(@traceid) where property=3
select @message =@message+isnull(' The stop time is "'+ convert(varchar(256),value,120)+'"',' There is no stop time.')+char(10)
from :: fn_trace_getinfo(@traceid) where property=4
select @message =@message+' The current status is '+ case convert(int,value) when 1 then 'RUNNING' when 2 then 'CLOSED' when 0 then 'STOPPED' end +'.'+char(10)
from :: fn_trace_getinfo(@traceid) where property=5
set @message=@message+char(10)+' '+char(10)
/*
Column ID int 应用筛选的列的 ID。
Logical Operator int 指定是否应用 AND 或 OR 运算符。
Comparison Operator int 指定比较类型（=、<>、<、>、<=、>=、LIKE 或 NOT LIKE）。
Value sql_variant 指定应用筛选的值。

select @message =@message+'The opening Trace ID is '+ltrim(str(@traceid))+'.'+char(10)
+'Its details are :'+char(10)
+' The options is '+ convert( varchar(256),value) +char(10)
from :: fn_trace_getfilterinfo(@traceid) where property=1
select @message =@message+' The file name is "'+ convert( varchar(256),value) +'.trc"' +char(10)
from :: fn_trace_getinfo(@traceid) where property=2
select @message =@message+' The max file size is '+ convert( varchar(256),value)+'MB' +char(10)
from :: fn_trace_getinfo(@traceid) where property=3
select @message =@message+' The stop time is "'+ convert(varchar(256),value,120)+'"'+char(10)
from :: fn_trace_getinfo(@traceid) where property=4
select @message =@message+' The current status is '+ case convert(int,value) when 1 then 'RUNNING' when 2 then 'CLOSED' when 0 then 'STOPPED' end +'.'+char(10)
from :: fn_trace_getinfo(@traceid) where property=5
set @message=@message+char(10)+' '+char(10)

*/
end
else
begin
set @message= 'The Store Procedure dbo.up_AutoTrace_report:'+char(10)
+'There is not exist the trace ID.'+char(10)
+'The inputing trace ID is:'+ltrim(str( @traceid))
goto finish
end
end

-------------
finish:
--print @message
--exec master.dbo.xp_logevent 60000,@message,INFORMATIONAL
return @return
go