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 shareexec @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 Filtersdeclare @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 finisherror: 
select ErrorCode=@rcfinish: 
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 

?