Black Box

The black box records query information into a trace file called blackbox.trc in the <SQL Server Path>\Data directory. This file is much like the C2-Level Auditing file, except it's on a smaller scale. The black box writes the trace file in 128K blocks. This means that after you start SQL Server with the black box trace writing, the trace file appears as 0K until it reaches the first 128K or until SQL Server stops.

To enable the black box, use the sp_trace_create and sp_trace_setstatus stored procedures with the parameter of 8 as shown here:

DECLARE @TraceID int EXEC sp_trace_create @traceid output, 8 EXEC sp_trace_setstatus @traceid, 1 

This trace records every query executed on the server, as well as any exception errors that occur. You can view the trace in Profiler after it is closed. The trace also records the following information:

• When the query executed

• Login name

• Application

• Hostname

• Error numbers with severity

• Windows NT user name and domain

• Database being connected to

The trace automatically stops and closes when SQL Server stops. The SQL Server stop can be clean or abrupt (a crash). You can also stop and close the trace manually by using the sp_trace_setstatus stored procedure:

EXEC sp_trace_setstatus 1,0 EXEC sp_trace_setstatus 1,2

The trace can be used to diagnose a server problem and to determine what is causing your server to crash. If you want the black box to start automatically when SQL Server starts, you can make the query that creates the black box into a startup stored procedure. When sqldiag.exe executes, the last 100 queries in the trace file are copied over to the <SQL Server Path>\Log directory with the sqldiag.txt. The trace file is then renamed SQLDiag.trc.

Note 

Since the black box takes valuable CPU resources away from your SQL Server, it can slow your server down. Keep the black box running only when you are troubleshooting.