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 

 

转载于:https://www.cnblogs.com/davidhou/p/7601329.html

  • 0
    点赞
  • 0
    收藏 更改收藏夹
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值