set nocount on;
use master;
go
if object_id('dbo.sp_perfworkload_trace_start') is not null
drop proc dbo.sp_perfworkload_trace_start;
go
create proc dbo.sp_perfworkload_trace_start
@dbid as int,
@tracefile as nvarchar(254),
@traceid as int output
as
--创建一个队列
declare @rc as int;
declare @maxfilesize as bigint;
set @maxfilesize=5;
exec @rc=sp_trace_create @traceid output,0,@tracefile,@maxfilesize,null
if(@rc!=0) goto error;
--设置要跟踪的事件
declare @on as bit;
set @on=1;
exec sp_trace_setevent @traceid,10,15,@on;
exec sp_trace_setevent @traceid,10,8,@on;
exec sp_trace_setevent @traceid,10,16,@on;
exec sp_trace_setevent @traceid,10,48,@on;
exec sp_trace_setevent @traceid,10,1,@on;
exec sp_trace_setevent @traceid,10,17,@on;
exec sp_trace_setevent @traceid,10,10,@on;
exec sp_trace_setevent @traceid,10,18,@on;
exec sp_trace_setevent @traceid,10,11,@on;
exec sp_trace_setevent @traceid,10,12,@on;
exec sp_trace_setevent @traceid,10,13,@on;
exec sp_trace_setevent @traceid,10,14,@on;
exec sp_trace_setevent @traceid,45,8,@on;
exec sp_trace_setevent @traceid,45,16,@on;
exec sp_trace_setevent @traceid,45,48,@on;
exec sp_trace_setevent @traceid,45,1,@on;
exec sp_trace_setevent @traceid,45,17,@on;
exec sp_trace_setevent @traceid,45,10,@on;
exec sp_trace_setevent @traceid,45,18,@on;
exec sp_trace_setevent @traceid,45,11,@on;
exec sp_trace_setevent @traceid,45,12,@on;
exec sp_trace_setevent @traceid,45,13,@on;
exec sp_trace_setevent @traceid,45,14,@on;
exec sp_trace_setevent @traceid,45,15,@on;
exec sp_trace_setevent @traceid,41,15,@on;
exec sp_trace_setevent @traceid,41,8,@on;
exec sp_trace_setevent @traceid,41,16,@on;
exec sp_trace_setevent @traceid,41,48,@on;
exec sp_trace_setevent @traceid,41,1,@on;
exec sp_trace_setevent @traceid,41,17,@on;
exec sp_trace_setevent @traceid,41,10,@on;
exec sp_trace_setevent @traceid,41,18,@on;
exec sp_trace_setevent @traceid,41,11,@on;
exec sp_trace_setevent @traceid,41,12,@on;
exec sp_trace_setevent @traceid,41,13,@on;
exec sp_trace_setevent @traceid,41,14,@on;
--设置筛选器
declare @intfilter as int;
declare @bigintfilter as bigint;
--应用程序名称筛选器
exec sp_trace_setfilter @traceid,10,0,7,N'SQL Server Profiler%';
--数据库ID筛选器
exec sp_trace_setfilter @traceid,3,0,0,@dbid;
--启动跟踪
exec sp_trace_setstatus @traceid,1;
--打印跟踪ID和文件名称供以后引用
print 'Trace ID: '+cast(@traceid as varchar(10))
+', Trace File: '''+@tracefile+'''';
goto finish;
error:
print 'Error Code: '+cast(@rc as varchar(10));
finish:
go
主要是红色代码部分，第一条是什么意思
第二条中我直接用1来替代@on也可以吧，就不申明@on这个参数
------解决方案--------------------
第一条我也没看懂啥意思，只看出来是个动态SQL，忘大神指点，学习。。。
第二条：这个是可以的，但是用1代替的话属于写死的写法，对于程序以后的扩展不是很有利，当然了，如果可以断定那个@on只会取1这个值，写死效率会更好些。
------解决方案--------------------
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
[ , [ @filecount = ] 'max_rollover_files' ]
0：好像是不返回错误
@tracefile：跟踪文件存放路径
@maxfilesize：跟踪文件的最大大小
NULL：实际上是stop_time的值，意味着要手动停止
这是用profiler导出的脚本，应该好理解一点：
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL