创建trace作业

我将发表一系列的用于创建、删除、查看trace的存储过程，可以将这些存储过程用于作业，以定期执行创建trace。

创建trace作业

use system --a user db for administration
go
if exists(select * from sysobjects where xtype='P' and name ='up_AutoTrace_create')
drop procedure dbo.up_AutoTrace_create
go

create procedure dbo.up_AutoTrace_create
@Path varchar(256) ='E:Trace', --路径,用于保存trace文件
@fileKeyWord varchar(256) ='DeadLock', --保存的trace文件名称中的关键字,以区别不同类型的trace
@stoptime datetime = '2005-07-16 19:00:00.000' , --Trace停止时间
@maxfilesize bigint = 20 , --Trace文件最大值,超过此数值则关闭当前Trace文件,并重新产生一个trace文件,以_n结尾
@options int = 2 , --trace选项
@events varchar(512) = '10,12,17,25,26,59', --要trace的事件,逗号为分隔符
@columns varchar(512) = '1,2,3,6,9,10,11,12,13,14,15,16,17,18,22,24,25,27,32', --要捕获的列
@filter1 Nvarchar(128) = N'10, 0, 7, SQL Profiler' , --筛选
@filter2 Nvarchar(128) = N'3, 0, 4, 7' , --database >=7
@filter3 Nvarchar(128) = N'22, 0, 4, 100' , --ObjectID>=100
@filter4 Nvarchar(128) = N'13, 0, 4, 500' , --duration>=500
@filter5 Nvarchar(128) = N'20, 0, 4, 24', --severity>=24
@filter6 Nvarchar(128) = '',
@filter7 Nvarchar(128) = '',
@filter8 Nvarchar(128) = '',
@filter9 Nvarchar(128) = ''

--文件名 :
--用途 :Trace作业
--输入参数 :
--返回值解释 :
--创建者 : summer.yang
--创建日期 : 2005-6-16
--修改者 :
--修改日期 :
--修改注解 :(引用请保留此信息)
--备注说明 :需要有执行xp_cmdshell的权限
/**********
--设置筛选: 0 =（等于） 1 <>（不等于） 2 >（大于） 3 <（小于） 4 >=（大于或等于） 5 <=（小于或等于） 6 LIKE 7 NOT LIKE
@filter6 = 10, 0, 7, SQLAgent - Generic Refresher
@filter7 = 10, 0, 7,SQLAgent - Job Manager
@filter8 = 10, 0, 7, SQLAgent - Alert Engine
@filter9 = 10, 0, 7, Lumigent Log Explorer

**********/
as
set nocount on
set @Path=ltrim(rtrim(@Path))
set @fileKeyWord=replace(@fileKeyWord,' ','')

if right(@path,1)<>''
begin
set @path=@path+''
end

--错误处理
declare @message nvarchar(4000)
declare @return int
set @return=0
set @message=''
---------------
--检查文件名关键字输入
if @fileKeyWord like '%%' or @fileKeyWord like '%/%' or @fileKeyWord like '%:%' or @fileKeyWord like '%*%' or
@fileKeyWord like '%?%' or @fileKeyWord like '%"%' or @fileKeyWord like '%%' or
@fileKeyWord like '%|%' or @fileKeyWord like '%;%' or @fileKeyWord like '%--%' or @fileKeyWord like '%''%'
begin
set @message ='存储过程up_AutoTrace_create:不能创建trace,无效的文件名关键字!'+char(10)
+'文件名关键字不能包含以下字符:'+char(10)
+' / : * ? " < > | ; -- '' '
set @return=-100
goto finish
end
--检查trace停止时间
if isdate(@stoptime)=0 or @stoptime < getdate()
begin
set @message ='存储过程up_AutoTrace_create:不能创建trace,无效的停止时间!'
set @return=-101
goto finish
end
--如果已经存在trace文件,不覆盖,检查文件的第14位(大写字母),按此字母增加
--一天之内启动trace最多只能26次
declare @date char(8), @letter char(1),@existLetter char(1)
declare @file varchar(26),@tracefile nvarchar(128)
declare @cmd1 nvarchar(4000)
declare @traceid int

set @letter ='A'
set @date = convert(char(8),getdate(),112)
set @file='Trace_'+@fileKeyWord+'_'+@date --注意不包含日期后的大写字母
set @cmd1='DIR '+@Path+@file+'*'

create table #tbl(
--id int not null identity,
string nvarchar(1000) null )
insert into #tbl
exec master.dbo.xp_cmdshell @cmd1
select @existLetter=max(substring(string,charindex(@file,string)+len(@file),1)) from #tbl
where substring(string,charindex(@file,string),len(@file))=@file

--判断已经存在的字母
if @existLetter like '[A-Z]'
set @letter=char(ascii(@existLetter)+1)
if @letter not like '[A-Z]'
begin
set @message= '存储过程up_AutoTrace_create:不能创建trace文件,可能今天创建的trace文件太多!'
set @return=-102
goto finish
end
set @tracefile=@path+@file+@letter

-------------
--如果已经启动一个或多个trace,再启动trace时要检查已经启动的trace的属性(捕获事件),如果事件类型一样则不启动
declare @tblEvents table(
eventid int not null ,
columnid int not null default 0
)
--要trace的事件
declare @lenEvents int,@estart int,@enext int,@lenColumns int
declare @event int, @column int, @cstart int, @cnext int
declare @cursor cursor
set @lenEvents = len(@events)
set @lenColumns = len(@columns)
select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)

while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin--跟踪的列column 嵌套循环
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
--往事件和列的变量表中插入数据
insert into @tblEvents(eventid,columnid)
select @event,@column
--循环值增长
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lenColumns + 1
if @cstart >@lenColumns set @cnext = 0
end
--重置初始值
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)

--循环值增长
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)
or columnid <=0
or columnid>=45

----与已经运行的trace进行比较,如果事件类型有相同的,则不启动trace
if exists(SELECT * FROM :: fn_trace_getinfo(0))
begin
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
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
set @message= '存储过程up_AutoTrace_create:不能创建Trace,因为已经存在相同的trace!'
set @return=-103
set @traceid=0
goto finish
end
fetch next from @cursor into @traceid
end
end
----------------
--创建trace
declare @on bit
set @on = 1
set @traceid = 0

--创建跟踪
exec @return = master.dbo.sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime
if (@return <> 0 or @@error<>0)
begin
if @return =0 set @return=-104
set @message='存储过程up_AutoTrace_create错误:调用sp_trace_create创建trace失败!'+char(10)
+'可根据存储过程返回值参考SQL Server联机丛书查找问题原因.'+char(10)
+'存储过程返回值:'+ltrim(str(@return))
goto finish
end
print @message
--添加一个事件或事件列
set @cursor=cursor forward_only static for
SELECT distinct eventid,columnid FROM @tblEvents
open @cursor
fetch next from @cursor into @event,@column
while @@fetch_status=0
begin
exec sp_trace_setevent @traceid, @event, @column,@on
if @return<>0 or @@error<>0
begin
if @return =0 set @return=-105
set @message='存储过程up_AutoTrace_create错误:调用sp_trace_setevent添加Trace事件或列失败!'+char(10)
+'可根据存储过程返回值参考SQL Server联机丛书查找问题原因.'+char(10)
+'存储过程返回值:'+ltrim(str(@return))
goto finish
end
fetch next from @cursor into @event,@column
end
-----------------
--添加筛选
declare @columnid int,@logical_operator int ,@comparison_operator int,@value nvarchar(128)

create table #filter(
string nvarchar(128) null,
columnid int null,
logical_operator int null,
comparison_operator int null,
value nvarchar(128) null
)
insert into #filter(string)
select @filter1 union all select @filter2 union all select @filter3
union all select @filter4 union all select @filter5 union all select @filter6
union all select @filter7 union all select @filter8 union all select @filter9
delete from #filter where string not like '%,%,%,%'
update #filter
set columnid=cast(substring(string,1,charindex(',',string,1)-1) as int),
logical_operator= cast(substring(string,charindex(',',string,1)+1,
charindex(',',string,charindex(',',string,1)+1)-charindex(',',string,1)-1 ) as int),
comparison_operator=cast(substring(string,charindex(',',string,charindex(',',string,1)+1)+1 ,
charindex(',',string,charindex(',',string,charindex(',',string,1)+1)+1)-charindex(',',string,charindex(',',string,1)+1)-1 ) as int),
value= ltrim(rtrim(substring(string,charindex(',',string,charindex(',',string,charindex(',',string,1)+1)+1)+1 ,
len(string)-charindex(',',string,charindex(',',string,charindex(',',string,1)+1)+1) )))

--select * from #filter
declare @value_nvarchar nvarchar(128),@value_bigint bigint,@value_int int, @value_datetime datetime
set @cursor=cursor forward_only static for
SELECT columnid,logical_operator,comparison_operator,value FROM #filter
open @cursor
fetch next from @cursor into @columnid ,@logical_operator ,@comparison_operator ,@value
while @@fetch_status=0
begin
--列的字段类型分类
if @columnid in(3,9,12,18,19,20,21,22,23,24,25,27,28,29,30,31,32,33,44)
begin
set @value_int=convert(int,@value)
exec @return = sp_trace_setfilter @traceid,@columnid,@logical_operator,@comparison_operator,@value_int
end
else if @columnid in(4,13,16,17)
begin
--select @columnid,@value
set @value_bigint=convert(bigint,@value)
exec @return = sp_trace_setfilter @traceid,@columnid,@logical_operator,@comparison_operator,@value_bigint
end
else if @columnid in(1,6,7,8,10,11,26,34,35,36,37,38,39,40,42)
begin
set @value_nvarchar=convert(nvarchar(128),@value)
exec @return = sp_trace_setfilter @traceid,@columnid,@logical_operator,@comparison_operator,@value_nvarchar
end
else if @columnid in(14,15)
begin
set @value_datetime=convert(datetime,@value)
exec @return = sp_trace_setfilter @traceid,@columnid,@logical_operator,@comparison_operator,@value_datetime
end
else if @columnid in(2,41,43)
begin
print '此列不能筛选:'+ltrim(str(@columnid))
end

if @return<>0 or @@error<>0
begin
if @return=0 set @return=-106
set @message='存储过程up_AutoTrace_create错误:调用sp_trace_setfilter添加筛选失败!'
goto finish
end
fetch next from @cursor into @columnid ,@logical_operator ,@comparison_operator ,@value
end

--启动指定的跟踪
exec sp_trace_setstatus @traceid, 1
--report trace status
exec dbo.up_AutoTrace_report @traceid,@message output
set @message=replace(@message,'The opening Trace','Trace is created. The trace')
--select * from :: fn_trace_getfilterinfo(@traceid)
--fn_trace_getfilterinfo
--返回有关应用于指定跟踪的筛选的信息。
-----------------
finish:
if @return<>0
begin
if @traceid<>0
begin
exec sp_trace_setstatus @traceid, 0 --停止跟踪
exec sp_trace_setstatus @traceid, 2 --关闭跟踪
end

raiserror (@message,16,1 )-- with log
return @return
end
else
begin
print @message
--exec master.dbo.xp_logevent 60000,@message,INFORMATIONAL
return @return
end
go