SQL Server 黑盒跟踪 -- 深入进阶
SQL Server 黑盒跟踪 -- 深入进阶
当你报告错误给支持提供商时,确保要提供Blackbox.trc和Sqldiag.txt文件。
创建Blackbox.trc文件:
1. 创建跟踪
2. 开启跟踪
3. 在运行sqldiag工具前停止跟踪
你可以用下面的存储过程,trace_blackbox,去创建,启动,然后停止黑盒跟踪。
拷贝下面的脚本到查询分析器,然后脚本,创建trace_blackbox存储过程。注意,如果你要在master数据库创建它,你必须在master数据库运行该存储过程。
if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[trace_blackbox]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1) drop procedure [dbo].[trace_blackbox] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE trace_blackbox @on int = 2 AS /* If no argument is passed to the @on parameter then get the current blackbox trace status. If @on is zero then stop and delete the blackbox trace. If @on is one then create and start the blackbox trace. */ declare @traceid int, @blackboxstatus int, @dir nvarchar(80) set @traceid = 0 set @blackboxstatus = 0 set nocount on SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 1 and value = 8 IF @on = 0 and @traceid > 0 begin select @blackboxstatus = cast(value as int) FROM :: fn_trace_getinfo(0) where traceid = @traceid and property = 5 IF @blackboxstatus > 0 exec sp_trace_setstatus @traceid,0 --stop blackbox trace exec sp_trace_setstatus @traceid,2 --delete blackbox trace definition end IF @on = 1 begin IF @traceid < 1 exec sp_trace_create @traceid OUTPUT, 8 --create blackbox trace exec sp_trace_setstatus @traceid,1 --start blackbox trace end set @traceid = 0 set @blackboxstatus = 0 SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 1 and value = 8 select @blackboxstatus = cast(value as int) FROM :: fn_trace_getinfo(0) where traceid = @traceid and property = 5 IF @traceid > 0 and @blackboxstatus > 0 begin select @dir = cast(value as nvarchar(80)) FROM :: fn_trace_getinfo(0) where traceid = @traceid and property = 2 select ‘The blackbox trace is running and the trace file is in the following directory.‘ select @dir + ‘.trc‘ end ELSE select ‘The blackbox trace is not running.‘ set nocount off GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
启动黑盒跟踪,在查询分析器执行如下命令:
trace_blackbox 1
检查黑盒跟踪的状态,可以执行如下命令:
trace_blackbox
停止黑盒跟踪,可以运行如下命令:
trace_blackbox 0
本文出自 “滴水石穿” 博客,请务必保留此出处http://ultrasql.blog.51cto.com/9591438/1583072
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。