sqlserver 创建对某个存储过程执行情况的跟踪

有时候需要抓取执行存储过程时某个参数的值，有时候程序调用存储过程执行后结果不太对，不确定是程序的问题还是存储过程的问题，需要单独执行存储过程看结果

即可用下面的方法

--================================================================================================
--创建对某个存储过程的执行情况的跟踪
--注意修改路径 和 object_id
--================================================================================================
-- Create a Queue
declare @rootPath NVARCHAR(200) = 'D:\TraceLog\' 
declare @path nvarchar(500)
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 20 
declare @intfilter int
set @intfilter =  object_id('dbo.sp_name')

SET @path = @rootPath --+ CONVERT(VARCHAR(6), GETDATE(), 112) + '\'  --年月的目录
    + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-',''),' ',''),':','');
--select @path  --D:\InOut\TraceLog\201709\20170927110926

-- 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, @path, @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 2, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on


-- Set the Filters

declare @bigintfilter bigint


--set @intfilter = 1055342824 --select object_id('dbo.sp_name')
exec sp_trace_setfilter @TraceID, 22, 0, 0, @intfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go


--================================================================================================
--查询跟踪到的结果 注意需要更改ID  id= @TraceID
--================================================================================================
declare @file nvarchar(256) 
select @file= path from sys.traces where id=@traceID and status=1 AND [path] IS NOT NULL
SELECT * FROM ::fn_trace_gettable(@file, DEFAULT) a
ORDER BY starttime desc



--================================================================================================
--停止跟踪、关闭跟踪并删除跟踪定义。  注意修改 @TraceID
--================================================================================================

SELECT * FROM ::fn_trace_getinfo(default)  

-- First stop the trace.   
EXEC sp_trace_setstatus @TraceID, 0  

-- Close and then delete its definition from SQL Server.   
EXEC sp_trace_setstatus @TraceID, 2