Have you ever had the problem where a user ran a query against your SQL Serer and crashed it or made the server unusable since the CPU was spiked at 100%? A SQL Server black box is the equivalent of a flight data record. The black box records all queries being passed to your SQL Server and other useful information like errors. You can use this information to determine why your server crashed or what error occured right before your CPU was pegged.

A black box traps the following information:

• Errors and warnings (Attention and Exception)

• Stored procedure execution (RPC: Starting)

• T-SQL execution (SQL: BatchStarting)

For those given events, the following information is trapped:

• Query or error that was executed

• Date and time executed

• The user that executed the query or sproc

• Database the event occured in

• The server or workstation that sent the query or caused the error

• Application name that performed the query

The black box writes to the file in 128K chunks. In other words, when you start the black box, it will appear as if the file has 0K until it has 128K worth of data to write. This architecture makes it a very efficient process and where it uses minimal CPU utilization. Because of that, you can run it for extended periods of time and not worry about it killing your server's performance. If you stop SQL Server, the SQL Server will write whatever data it has in cache to the trace file.

The file is written out to the data directory by default (\Program Files\Microsoft SQL Server\MSSQL\data) and is called blackbox.trc. As the file grows to 5MB, it rolls over to a new file. A new file will be created also occurs if you start and stop SQL Server, overwriting your old file. Make sure that you occasionally go through and delete the old trace file that may've rolled over.

Now that you know what a black box is, how do you start one? To start one, you have to use the sp_trace_create system stored procedure. You set the trace type to the special server-side trace status of 8 to make it a black box trace file. The below stored procedure will create a black box stored procedure called startblackbox that can be used to start the black box on demand. I have optionally used the sp_procoption stored procedure to start the black box when SQL Server starts.

-----------------------------------------------------------------------------------
USE master
GO
CREATE PROC startblackbox
AS
declare @TraceID int
exec sp_trace_create @TraceID output, 8
exec sp_trace_setstatus @traceID, 1
RETURN
go
--Optional part of syntax to make the black box start on the startup of SQL Server
exec sp_procoption startblackbox, 'startup', 'on'
go
-----------------------------------------------------------------------------------

Now that you have the stored procedure, give it a shot on a development server! You can use the output in many ways. For example, you can use the output for Index Tuning Wizard input to tune your server. The main reason for this trace is to send to Microsoft product support or for debugging yourself.