停止trace的存储过程

停止trace的存储过程

这个存储过程和创建trace的存储过程配合，可以非常灵活的控制trace的创建和停止。一般地，在作业中创建trace，可以设定trace运行的时间，到时间trace自动停止，不需要使用这个存储过程停止。用这个存储过程，是可以手动停止trace的运行。

use system
go
if exists(select * from sysobjects where xtype='P' and name ='up_AutoTrace_stop')
drop procedure up_AutoTrace_stop
go

create procedure up_AutoTrace_stop
@traceid int =0, --默认全部停止
@events varchar(512) = '' --要trace的事件,逗号为分隔符
as
/**********
--文件名 :
--用途 :停止Trace作业
--输入参数 :
--返回值解释 :
--创建者 : summer.yang
--创建日期 : 2005-6-18
--修改者 :
--修改日期 :
--修改注解 :(引用请保留此信息)
--备注说明 :
**********/

set nocount on

--错误处理
declare @message nvarchar(4000)
declare @return int
set @return=0
set @message=''
---------------
--如果不存在打开的trace,则直接结束
if not exists(SELECT * FROM :: fn_trace_getinfo(0))
begin
set @message='There is no active trace in this server.'
goto finish
end
-------------
declare @cursor cursor
--如果@traceid>0,则停止@traceid,
if @traceid>=1
begin
if exists (SELECT * FROM :: fn_trace_getinfo(@traceid))
begin
exec sp_trace_setstatus @traceid, 0 --停止跟踪
exec sp_trace_setstatus @traceid, 2 --关闭跟踪
set @message= '存储过程up_AutoTrace_stop: 成功停止一个Trace,此trace的id为:'+ltrim(str( @traceid))
end
else
begin
set @message= '存储过程up_AutoTrace_stop: 无此traceid,输入的traceid为:'+ltrim(str( @traceid))
set @return=-96
goto finish
end
end
else if @traceid=0
begin --根据@events的值判断
if len(@events)>0
begin--009
declare @tblEvents table( eventid int not null )
declare @lenEvents int,@estart int,@enext int
set @lenEvents = len(@events)
select @estart = 1
select @enext = charindex(',',@events,@estart)
--输入的trace的事件
while @enext > 0
begin
insert into @tblEvents
select cast(substring(@events,@estart,@enext-@estart) as int)
--循环值增长
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @lenEvents + 1
if @estart > @lenEvents set @enext = 0
end
--删除不正确的事件id
delete from @tblEvents
where eventid<=9
or eventid>=119
or eventid in (29,31,32,48,49,56,57,62,63,64,65,66,99,101)
if not exists (select * from @tblEvents)--不存在有效的事件
begin
set @message='存储过程up_AutoTrace_stop: 输入的事件类型无效!'+char(10)
+'输入的事件类型是:'+@events
set @return=-98
goto finish
end
end--009

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--008
if len(@events)>0
begin--如果@traceid=0,而@events有合法的事件,则查询相应的@traceid停止
if not exists (
select * from @tblEvents a
full outer join (SELECT distinct eventid FROM ::fn_trace_geteventinfo(@traceid)
) b
on a.eventid=b.eventid
where a.eventid is null
or b.eventid is null )
begin
exec sp_trace_setstatus @traceid, 0 --停止跟踪
exec sp_trace_setstatus @traceid, 2 --关闭跟踪
set @message= '存储过程up_AutoTrace_stop: 成功停止一个Trace,此trace的事件列为:' +char(10)+@events
goto finish
end

end
else--如果@traceid=0,而@events没有合法的事件,则全部停止
begin
exec sp_trace_setstatus @traceid, 0 --停止跟踪
exec sp_trace_setstatus @traceid, 2 --关闭跟踪
print @message
set @message= '存储过程up_AutoTrace_stop: 成功停止一个Trace,此trace的id为:'+ltrim(str( @traceid))
end

fetch next from @cursor into @traceid
end--008
if len(@events)>0
begin
--没有找到对应的事件列
set @message='存储过程up_AutoTrace_stop: 没有找到对应事件列的trace,未停止任何trace.'+char(10)
+'要查找的事件列为:'+char(10)+@events
set @return=-97
goto finish
end
end

-----------------
finish:
if @return<>0
begin
raiserror (@message,16,1 ) --with log
return @return
end
else
begin
print @message
--exec master.dbo.xp_logevent 70000,@message,INFORMATIONAL
return @return
end
go