DaveWentzel.com            All Things Data

BlackBox

 

BlackBox Information
Here is the blackbox info.  You need to run the following code first. 
DECLARE @rc int

,@TraceID int
EXEC @rc = sp_trace_create @TraceID output , 8

SELECT TraceID = @TraceID , error = @rc

EXEC sp_trace_setstatus @TraceID , 1

SELECT * FROM ::fn_trace_getinfo(@TraceID)
This creates the server-side trace.  It needs to be rerun whenever the sql service is restarted.  The easiest way to do this is to create a sqlagent job with an "On Start" schedule that runs the code.  So, whenever the agent starts it begins tracing.  You can run it more than once, but it will generate an error 12 in the output, which can be ignored.  The output will also show you where the trace datafile will be written.  It is generally written to the \DATA folder with a name of blackbox.trc.  This file will contain the last 5MB of buffered sql commands that the server processed.  You might notice that the filesize stays at 0KB…that's OK, sqlserver only writes the data from its buffers when it needs to (service stop, etc).  After that 5MB is written out a new file named blackbox_1.trc will be written to.  The two files will then be written to cyclically.  Running blackbox only shows a minimal performance hit since this is a server-side trace instead of a standard profiler trace and we are only buffering 5MB. 
When you experience server problems again in the future the easiest thing to do is to run sqldiag from mstsc.  Unfortunately sqldiag is usually not in the PATH.  You can find it under %PROGRAM FILES%\Microsoft SQL Server\MSSQL\Binn, usually.  Sqldiag will write out a number of files in the \LOG folder containing error logs, locking and processes info, memory reports, etc.  And if blackbox is running it will contain sqldiag.trc which is a copy of blackbox.trc.  Sqldiag can be run even if the services are stopped, which is good for troubleshooting. 
I would suggest you run the queries above, schedule the sqlagent job, wait a few minutes, then execute sqldiag and check the output folder to ensure all is working. 
Another Script
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_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 sp_trace_blackbox

(@on int = 2)

AS

/*

Name:  sp_trace_blackbox.sql

Author:  Dave Wentzel

Create Date: 20040326

Purpose: runs the blackbox for SQL2K, SP3a

Known Issues: @on = 0, stop and delete the blackbox trace

  @on = 1, create and start the trace

  @on = 2 or null, get the current blackbox status
Developer History: 

Name  Change Date  Change Notes

------------------------------------------------------

Dave Wentzel 20040326  began script. 

sp_trace_blackbox 1

sp_trace_blackbox 0
*/
--declare and initialize my variables.  

declare @traceid int

 , @blackboxstatus int

 , @dir nvarchar(80)
select @traceid = 0

select @blackboxstatus = 0
set nocount on
--8 tells me this is a blackbox trace installed on the server. 

SELECT @traceid = traceid

FROM :: fn_trace_getinfo(0)

where property = 1 and value = 8
--a blackbox exists and I wish to whack it

IF @on = 0 and @traceid > 0

BEGIN

 --we know it exists, but is it running?

 select @blackboxstatus = cast(value as int)

 FROM :: fn_trace_getinfo(0)

 where traceid = @traceid and property = 5
 --it is running, stop it

 IF @blackboxstatus > 0

 BEGIN

  exec sp_trace_setstatus @traceid,0

 END
 --regardless of whether it is running, delete the definition

   exec sp_trace_setstatus @traceid,2

END
--we were asked to create and start it. 

IF @on = 1

BEGIN

 --just double check that it does not exist

     IF @traceid < 1

 BEGIN

  --it does not exist, create the definition for a blackbox

  exec sp_trace_create @traceid OUTPUT, 8

 END
 --regardless of whether it existed or not, we can now start it

     exec sp_trace_setstatus @traceid,1

END
--give me some general info now

--reinitialize just in case

select @traceid = 0

select @blackboxstatus = 0
--search for an 8 which is a blackbox-style trace

SELECT @traceid = traceid

FROM :: fn_trace_getinfo(0)

where property = 1 and value = 8
--what's the status?

select @blackboxstatus = cast(value as int) FROM :: fn_trace_getinfo(0)

 where traceid = @traceid and property = 5
--I have a trace and it is at least defined

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

BEGIN

 select 'The blackbox trace is not running.'

END


set nocount off
GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO
 
 

Add new comment