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.
