The black-box trace is a trace that is used by Microsoft when trying to diagnose problems. It’s a pretty lightweight trace that captures only the SP:Starting, SQL:BatchStarting, Exception, and Attention events (see here).
This trace however, does not survive a restart of the instance. You must re-start the trace if the instance bounces.
Here’s how you declare the trace
--Declare the trace DECLARE @trcID INT; EXEC sp_trace_create @trcID OUTPUT, @options = 8; SELECT @trcID AS TraceID GO
In the @options, 8 means this will be a black-box trace. Query the trace to ensure that it was created successfully. Mind you, even though you’ve created the trace, it does not start when created. You need to start it manually later using sp_trace_setstatus (see below).
--Get trace info SELECT * FROM fn_trace_getinfo (2); GO
Here are my results:
Here are the properties returned by the trace:
- Property 1: Trace options. Rather than me explain them here, see this link.
- Property 2: File name. This is not configurable for a black-box trace.
- Property 3: Max File size in MB. Again, not configurable for a black-box trace.
- Property 4: The stop time for the trace. Null means run until the trace is stopped manually.
- Property 5: The traces’ current status. 1 = ON, 0 = OFF.
I’m focusing specifically on capturing DML statements here, so let’s start the trace:
--Start the trace EXEC sp_trace_setstatus @traceid = 2, @status = 1; GO
The @status = 1 indicates that you want the trace started. If you want to stop the trace, simply replace the @status = 1 to @status = 0.
Now, let’s look the information being brought back in the trace for the events:
--You can get the path from the 'Get trace info' select above SELECT * FROM fn_trace_gettable ( 'G:\Sql_2008_Data\blackbox.trc', DEFAULT); GO
I’ll refrain from posting a screenshot. If you ran the SELECT statement above, you’ll see that it captures quite a lot of data. More than enough for you to be able to mine whatever data you’re looking for, in a general sense. Like I said, this is a high-level lightweight trace.
So, focusing on DML. I’ve created a table called TestTable in my database consisting of an ID field and a Value field of type VARCHAR(100). Let’s insert 100 rows into this table:
--Create table & test data CREATE TABLE dbo.TestTable( ID INT NOT NULL PRIMARY KEY IDENTITY(1,1), Value VARCHAR(100) NOT NULL ) INSERT INTO dbo.TestTable(Value) SELECT 'Test' GO 100
Now, let’s see if we can see this in the trace:
SELECT * FROM fn_trace_gettable ( 'G:\Sql_2008_Data\blackbox.trc', DEFAULT) WHERE TextData LIKE '%CREATE TABLE%' ORDER BY StartTime DESC GO
Yup, got it:
Now, let’s delete some rows and see if we can capture that:
--Do some DML DELETE FROM dbo.TestTable WHERE ID > 50
Yup, got that too:
The black-box trace is a very handy for capturing basic info for auditing on your servers. Should you leave it on all the time? That’s up to you. I don’t keep it running at all times, but there are times that I wish I had. You do have other resources to find (for example, DML) operations that have been run lately on your sql servers without having the default trace enabled, as Jack Vamvas (blog | twitter) points out in his blog post, but these depend upon CDC or Change Tracking to be enabled. I believe most people don’t have either of these enabled on all their sql servers. The fn_dblog() function is available on all servers, but unfortunately it can tell you what was deleted (although you do have to do some digging to parse it out…hmm…next blog post methinks), it cannot tell you who deleted it. Just FYI, I’m in no way slamming Jack’s wonderful post. He’s got one of my favorite blogs to follow. Concise, to the point, and no fluff. I hate fluff.
Oh, what’s that? I made you create all these traces and whatnot and you don’t know how to get rid of them? Sigh, here you go. Be a good citizen and always clean up after yourself.
--Stop the trace EXEC sp_trace_setstatus @traceid = 2, @status = 0; GO --Delete the trace EXEC sp_trace_setstatus @traceid = 2, @status = 2; GO
@status = 0 stops the trace, @status = 2 deletes the trace. If you try to delete a trace without stopping it first, you’ll get an error:
I think there was a Redd Foxx joke about going to Dallas…but I digress..
The folks at sqlskills.com are offering up free tuition to their 5-day Internals and Performance class in Dallas, February 21-25. I would kill for a chance to take this class. (seriously, email me at email@example.com). I’m not too sure about the bottomless tea part though. I would imagine it would burn the nethers badly.
I’ve been absolutely devouring all the content that these guys have been throwing out lately, and would love a chance to learn it straight from the authors themselves. I’ve watched pretty much all of their MCM training videos (some twice!). I’ve also been studiously going through the Sql Server 2008 Internals book that they’ve co-authored. Honestly, I didn’t know how much I didn’t know until I recently committed to learning everything I can about Sql Server.
Now, why I deserve this:
- I recently have stepped up into the Senior DBA position at my place of employment, hence my studiousness.
- Since undertaking said studiousness, I’ve found a forgotten love for learning. I’m really really enjoying learning all the internals of Sql Server. (I’m a born-again DBA of sorts I guess).
- My company won’t pay for it.
- I want to become a Sql Server MCM. I’m trying really hard to get to that level.
- I just want to know everything about Sql Server! I’m a sponge.
- I’m really excited about learning from Paul, Kimberly, Bob and even Brent!