Monitoring the Blackbox Trace
When you first enable Blackbox tracing, you'll see the blackbox.trc file in the \data directory, but the file size might appear to stay at 0 initially. In fact, SQL Server Profiler probably won't report a positive size for the blackbox.trc file until SQL Server has executed enough batches to increase the file size to at least 128KB.
SQL Server captures the trace data to an internal location and writes the file to the disk in 128KB chunks. In addition, sqldiag captures only the data that SQL Server has written to the blackbox.trc file, and that file might not include the most recent queries. By default, any query batches submitted after SQL Server writes the last 128KB chunk aren't available in the blackbox.trc file.
When SQL Server stops, either because of an error or because someone manually stops it, SQL Server writes any remaining captured trace data to the blackbox.trc file from the internal location. If you want all the trace data to be available without having to stop the SQL Server service, you must stop and close the Blackbox trace. You can do this with two calls to the sp_trace_setstatus procedure.
To stop and close the trace that has the trace ID of 1 (the trace I showed you in the previous section), you can execute the following statements:
EXEC sp_trace_setstatus 1, 0
EXEC sp_trace_setstatus 1, 2
Setting the status to 0 stops the trace; setting the status back to 1 can restart it. You can set the status to 2 to close the trace, but you must already have stopped it. You can't restart a closed trace; you must completely recreate it. When you've closed the trace, the complete blackbox.trc file is available. Subsequently running sqldiag copies the complete blackbox.trc file to the \log directory as sqldiag.trc. (BOL incorrectly states that sqldiag captures only the last 100 queries; that number is accurate for SQL Server 7.0, but not for SQL Server 2000.)
The Blackbox trace is automatically configured for a maximum file size of 5MB and will roll over and start a new file called blackbox_1.trc (which BOL incorrectly reports as blackbox_01.trc) when the file exceeds the initial 5MB. If this second trace file exceeds 5MB, the trace reverts to writing to the original blackbox.trc file. You always have the last 5MB of trace information available—and up to 10MB if you have a rollover. Because the Blackbox is intended to save only the most recent queries, 10MB is usually sufficient. If you want to save more than 10MB of queries, consider running a trace independent of the Blackbox.
Automatic Tracing
Because you never know when you might have a serious error and need the contents of the Blackbox, you can enable Blackbox tracing to start automatically when SQL Server starts. To do this, you need to create the trace within a stored procedure and use the sp_procoption procedure to mark the trace for autostart, as the code in Listing 1 shows.
Be aware that having a constantly running trace might negatively affect your server's performance. I don't have any numbers yet to quantify this impact, which is far smaller than in SQL Server 7.0, primarily because of the redesign of the tracing mechanism. The only events the Blackbox trace captures are the query batches sent from clients to SQL Server, and capturing batches is one of the least expensive events to trace. Nonetheless, tracing isn't free in terms of resource usage, and if you're trying to tune your SQL Server for maximum performance, you need to consider this added cost.
This article isn't a complete discussion of server-side tracing in SQL Server 2000, but it does alert you to the Blackbox's new behavior and capabilities to record SQL Server's most recent queries. The Blackbox tracing feature is incredibly useful for troubleshooting, but SQL Server 2000 BOL's discussion about the feature is seriously limited. The information I've presented can fill in the gaps and give you the information you need to take advantage of this tool.
End of Article
Prev. page
1
[2]
next page -->
