SQL Server's 'black-box' flight recorder

SQL Server's 'black-box' flight recorder

Rate This
  • Comments 10

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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\blackbox_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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\blackbox_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.

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • Paul Randal writes: "Kimberly mentioned that SQL Server has a 'black-box' trace, similar to an aircraft

  • 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

  • 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.

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

  • 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...)

  • Hi, Paul

    ""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.""

    It looks like once you set the status to run, it will just run and not only when SQL Server crahses.

    It produces two 5MB blackbox trace and rollver once 5MB is reached.

    As Saggi saide, what is the diff between the default trace and this black box trace? diff events i assume.

    Thank you,

    Bill Kan

  • Paul,

    I have listened to you and Kim speak on SSWUG and had to check out your blog.  I am really glad I did, the "black box" is such a neat idea that I implemented it on all my servers.

    Thanks and I look forward to more cool stuff!

    Jason Love

    P.S.   Where do I get the block game at? (U.S.)

  • Here's a link (http://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx) to another MS blog that lists everything in the default trace.

    Yup - looks like the black-box actually does 2 x 5MB files - the docs are incorrect.

    You can buy Blokus at amazon.com or any toy store.

  • Say you have a serious performance problem and few times you will have assertion issue with a dump files

  • 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.

Page 1 of 1 (10 items)