Black Box Trace In SQL Server

The other day I was looking on a production SQL Server and I noticed a trace file that was running but was not able to associate it to SQL Profile session. I queried sysprocesses and was unable to find a spid that was running SQL Profile. I was perplexed for a little while and started searching the Internet high and low and came across “black box” traces in SQL Server.

So what I did was… I opened up the trace file in SQL Profiler and I was able to view the events and columns for the trace. Then I compared the events and columns to what I found in the trace file to what I found on the Internet. The comparison turned out to be an exact match for events as well as columns. So I started asking around and nobody seemed to know how it got there on the server and if it was being used. Of course when it comes to something like this nobody wants to fess up.

Here is the idea of a “black box” trace. A black box trace records the last 5MB of activity on the SQL Server when it becomes problematic. The trace captures the queries and\or errors right before the server becomes unrepsonsive or crashes. This information is useful for sending to Microsoft to help diagnose the problem. The black box trace records the following:
Stored Procedure Execution (RPC:Starting)
T-SQL Batch Execution (SQL:BatchStarting)
Errors and Warnings (Attention and Exception)

You can go to google and query “sql server black box trace” and you will get a lot more information. From what I have read and I can tell on my own server the black box trace does not cause any performance issues. If you have experience using the black box trace feature please post and let us know!