How Can You Use It?
To get programmatic access to the list of events and data that the default trace captures, you can use the fn_trace_geteventinfo function. You need to know the trace ID number, which you can get by using the fn_ trace_getinfo system function. Like fn_trace_ gettable, fn_trace_geteventinfo doesn't return event names. In fact, fn_trace_geteventinfo returns only two columns: an event ID number and a column ID number.
You can manually translate these numbers by looking at the sp_trace_setevent documentation in the BOL, but doing so can be tedious when there are many events and data columns. Fortunately, SQL Server 2005 provides two new catalog views, called sys.trace_events and sys.trace_columns.You can use the syntax that Listing 2 shows to join these views with the output from fn_trace_geteventinfo and determine which columns and data the default trace returns.
When I ran the query that Listing 3 shows, I got 695 rows of output. I won't include all those rows here, but because I had the information in tabular format, I was able to group the data by event and show you how many data columns were returned for each event. Figure 2 shows the results. Aside from the object creates and drops that I saw on my SQL Server 2005 system, the default trace captures almost all types of errors, memory and disk file-size changes, security changes, and Fulltext changes. I'm not sure why Fulltext changes would be included in a default trace that's always running, but the other data represents the kinds of changes you might want to know about. In addition, these events aren't likely to occur nearly as often as batches are submitted, so my guess is that the growth rate of the default trace file will be much slower than that of the Blackbox trace file.
The bad news is that if you want a slightly different set of events or data than what the default trace includes, or if you want to store the trace files in an alternative location, you'll have to create your own trace. Although the procedure sp_trace_setevent typically lets you turn specific events or data columns on or off for a running trace, that capability isn't available for the default trace. Any attempt to use the procedure results in the error Msg 19070, Level 16, State 2, Procedure sp_trace_setevent, Line 1—The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.
Heads Up
Will SQL Server 2005's new default trace enabled option be useful? That's your call, but I think you'll often be grateful to have it up and running.The most important thing is to be aware of this trace so that you can decide how to handle it.