Microsoft KB Archive/281671

= INF: Stored Procedure to Create a SQL Server 2000 Blackbox Trace =

Article ID: 281671

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q281671



SUMMARY
When you report an error to your primary support provider, make sure that you provide the Blackbox.trc and Sqldiag.txt files. To create the Blackbox.trc file:
 * 1) Create a trace.
 * 2) Start the trace.
 * 3) Stop the trace before you run the sqldiag utility.



MORE INFORMATION
You can use the following stored procedure, trace_blackbox, to create, start, and then stop a blackbox trace.

Copy the script that follows to a SQL Server Query Analyzer window, and then run the script to create the trace_blackbox stored procedure. Note that if you create the trace_blackbox stored procedure in the master database that you must be in the master database to run the stored procedure. 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 To start the blackbox trace, type the following command in a SQL Server Query Analyzer window: trace_blackbox 1 To check the status of the blackbox trace, issue the following command: trace_blackbox To stop the blackbox trace, issue the following command: trace_blackbox 0

Keywords: kbinfo KB281671

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.