SQL Server black box Trace--Deep advanced

SQL Server black box Trace--Deep advanced

When you report an error to the support provider, make sure to provide the BLACKBOX.TRC and Sqldiag.txt files.

To create a blackbox.trc file:

1. Create a Trace

2. Turn on tracking

3. Stop tracking before running the SQLdiag tool

You can use the following stored procedure, Trace_blackbox, to go to create, start, and then stop the black box trace.

Copy the following script to Query Analyzer, and then script to create the trace_blackbox stored procedure. Note that if you are creating it in the master database, you must run the stored procedure in the master database.

if exists  (select * from dbo.sysobjects where id = object_id (N ' [ DBO]. [Trace_blackbox] ')  and objectproperty (id, n ' isprocedure ')  = 1) drop procedure &NBSP;[DBO]. [Trace_blackbox] Goset quoted_identifier ongoset ansi_nulls ongocreate 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 set  @traceid  = 0set  @blackboxstatus  = 0set nocount onSELECT  @traceid  =  TracEid from :: fn_trace_getinfo (0) where property = 1 and value =  8IF  @on  = 0 and  @traceid  > 0beginselect  @blackboxstatus   = cast (value as int)  from :: fn_trace_getinfo (0) where traceid = @ traceid and property = 5if  @blackboxstatus  > 0 exec sp_trace_ setstatus  @traceid,0 --stop blackbox traceexec sp_trace_setstatus  @traceid, 2  --delete blackbox trace definitionendIF  @on  = 1beginIF  @traceid   < 1 exec sp_trace_create  @traceid  OUTPUT, 8 --create blackbox  traceexec sp_trace_setstatus  @traceid,1 --start blackbox traceendset  @traceid  = 0set  @blackboxstatus  = 0SELECT  @traceid  = traceid from ::  fn_trace_getinfo(0) where property = 1 and value = 8select  @blackboxstatus  =  Cast (Value as int)  from :: fn_trace_getinfo (0) where traceid =  @traceid  and property = 5IF  @traceid  > 0 and  @blackboxstatus  >  0beginselect  @dir  = cast (Value as nvarchar)  from :: fn_trace _getinfo (0) where traceid =  @traceid  and property = 2select  ' the  blackbox trace is running and the trace file is in the  Following directory. ' select  @dir  +  '. TRC ' endelse select  ' The blackbox trace is not  running. ' Set nocount offgoset quoted_identifier offgoset ansi_nulls ongo

To start the black box trace, execute the following command in Query Analyzer:

Trace_blackbox 1

To check the status of the black box trace, you can execute the following command:

Trace_blackbox

To stop the black box trace, you can run the following command:

Trace_blackbox 0

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1583072

SQL Server black box Trace--Deep advanced