SQL Server's 'black-box' flight recorder

So I learned something about SQL Server at TechEd today. In Kimberly and Bob Beauchemin's pre-con, Kimberly mentioned that SQL Server has a 'black-box' trace, similar to an aircraft flight-recorder, which I'd never heard of. It's an internal trace that has the last 5MB of various trace events and it's dumped to a file when SQL Server crashes. This can be really useful if you're troubleshooting an issue that causing SQL Server to crash or someone or something is telling SQL Server to shutdown and its unclear who or what is doing it.

I did a little investigation on this and figured out how to turn it on. First we need to create the trace:

DECLARE

@traceid INT;

EXEC

 sp_trace_create @traceid OUTPUT, @options = 8;

SELECT

 @traceid AS [Trace Id];

GO

And on my system the trace ID returned is 2. The magic number here is the 8 - it specifies that the trace is a black-box - and this is documented in the Books Online for sp_trace_create.

Let's look at the trace properties:

SELECT

* FROM fn_trace_getinfo (2);

GO

This returns the following:

traceid property value

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

2 1 8

2 2 \?C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAblackbox_1.trc

2 3 5

2 4 NULL

2 5 0

The various properties are:

• 1: the trace options. 8 means its a black-box trace.
• 2: the filename of the trace file. This isn't configurable for a black-box trace.
• 3: the maximum size of the file in MB. This also isn't configurable for a black-box trace.
• 4: the stop time for the trace. NULL means the trace won't stop until its manually turned off.
• 5: the current trace status. 0 is off, 1 is on.

Notice that the trace status is 0, which means that the trace hasn't been started - so we need to start it manually using:

EXEC

sp_trace_setstatus @traceid = 2, @status = 1;

GO

Very cool. Next I wanted to see whether this trace would start up again automatically if I bounced the SQL instance I'd defined it on - so I did a net stop mssqlserver and net start mssqlserver. Just for kicks I thought I'd look in the black-box to see what it had logged when I did the net stop mssqlserver. To do that I used the following:

SELECT

* FROM fn_trace_gettable (

    'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAblackbox_1.trc',

    DEFAULT);

GO

And then ran the fn_trace_getinfo query again. Nothing. So - if you want this feature to be on all the time, you'll need to write a script that's run at instance startup that defines and enables the trace.

Comments

• Anonymous
  June 03, 2007
  The comment has been removed

• Anonymous
  June 03, 2007
  Hi Paul The easiest way to make the trace start automatically is to create a procedure in the master database that does nothing but call sp_trace_create with option 8. Then you can use sp_procoption to mark the procedure for 'autostart'. It's one of the first things I do every time I install a new SQL Server.  The blackbox can have up to 10MB of trace data stored, in 2 5MB files.   And, in SQL 2005, a much easier way to look at trace properties is by querying sys.traces. It returns a lot more information about each trace than fn_trace_getinfo.  Check it out! Thanks for all you do Kalen

• Anonymous
  June 04, 2007
  Thanks Kalen. Sys.traces looks much better - maybe I've got an old version of BOL but the various trace entries in it don't point at the new catalog views.

• Anonymous
  June 04, 2007
  Well, I'm not sure where May went but I do know that part of it went to my 2 weeks in Hyderabad, India

• Anonymous
  June 06, 2007
  Hi Paul, How does this "blackbox" trace differ from SQL 2005 default trace? This one is enabled by default on server start. You can stop it with exec sp_configure 'show advanced options',1 RECONFIGURE exec sp_configure 'default trace enabled', 0 RECONFIGURE I think that just like the blackbox trace, you cannot change the trace filename, the maximum file size or the fact that it never stop (until you disable it...)

• Anonymous
  June 14, 2007
  The comment has been removed

• Anonymous
  June 15, 2007
  The comment has been removed

• Anonymous
  June 17, 2007
  The comment has been removed

• Anonymous
  July 01, 2007
  Say you have a serious performance problem and few times you will have assertion issue with a dump files

• Anonymous
  January 10, 2010
  The below URL should also be helpful http://technet.microsoft.com/en-us/library/cc293615.aspx. Also this mentions the Maximum File size and the trace file location options.