INF: Stored Procedure to Create a SQL Server 2000 Blackbox Trace (281671)

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