Solving the mystery of SQL Server 2005's default trace enabled option
As I was researching new server-configuration options in SQL Server 2005, I came across an option called default trace enabled. I checked the SQL Server Books Online for the current build, and found the following information: Use the default trace enabled option to enable or disable the default trace log files. The default trace functionality provides a rich, persistent log of activity and changes primarily related to the configuration options.
The BOL states that the default trace's purpose is to give database administrators the necessary log data to diagnose a problem the first time it occurs. The option is enabled by default, unlike most other settable options in SQL Server 2005. (Microsoft seems to have made a concerted effort not to enable by default anything that isn't 100-percent necessary.) Because this is an advanced option, by default it doesn't show up in the list of configuration options when you run sp_configure. But I could find no list of which events the enabled trace captures, so I had no idea how useful it might be.
What Does It Do?
Because the trace is enabled by default, I was able to use the fn_trace_gettable function to look at the captured trace data. In SQL Server 2005, this function lets you see the contents of a currently active trace file. (The function is also available in SQL Server 2000 but can return data only from a trace file that has already been stopped and closed.)
The BOL told me that by default, the trace file is called log.trc and resides in the SQL Server installation directory's LOG subdirectory. With that information, I was able to run the code that Listing 1 shows.This code returned a variety of trace events but didn't give me the event-type name, only the event number.
I tried opening the trace directly in SQL Server Profiler. This time, the output revealed the trace events' names.The majority seemed to be either Object:Created or Object:Deleted events, but these were the only traced events that had occurred on my new SQL Server 2005 system.To find out which other events the trace can capture, I needed to look at the trace's properties.
Because I wanted more information about the trace than I could find through Profiler, I sent an email to a Microsoft engineer with whom I've been working. He responded as if I was asking about the default trace template that you'd use when defining a trace through Profiler. I was concerned that this person, who knows so much about so many of the new SQL Server 2005 features, didn't seem to know anything about the new default trace enabled configuration option.
What Are the Limits?
This default trace was beginning to sound suspiciously like SQL Server 2000 Profiler's Blackbox trace (see "Profiler's Blackbox Feature," January 2001, InstantDoc ID 16078).The problem with the Blackbox trace is that it can capture too much information. The trace keeps track of every batch submitted to a SQL Server, so if something unexpected happens (e.g., a crucial table disappears, the server seems to hang), you can inspect the trace's output for clues to the problem. The Blackbox trace is designed to fill a 5MB file called blackbox.trc. When that file fills up, the trace starts a second file called blackbox_01.trc.When the second file fills, the trace recycles the first file so that you never have more than 10MB of trace data. Every time SQL Server starts, it starts writing the record of batches being executed to blackbox.trc. An extremely busy system can generate significantly more than 10MB of batch information by the time you detect and begin tracking an unexpected event—meaning that the event record you need will already have been deleted.
I did a little more investigating and found that the Blackbox trace capability is still available in SQL Server 2005.The default trace enabled option gives you another simple trace for capturing important information and doesn't appear to have the limitations of the Blackbox trace.
To determine the properties of the new default trace, I used the system function fn_trace_getinfo. By default, the trace is enabled, so when I executed the statement
SELECT * FROM ::fn_trace_getinfo(default)
I got the results that Figure 1 shows. (I might have gotten back more rows if I'd defined additional traces.)
This function lists five properties for each currently defined trace, corresponding to the five rows of output. Property 1 shows the options that have been set for the trace; a value of 2 means that the trace file is enabled for rollover to a new file when the maximum size is reached. Property 2 shows the trace file's location (which I've abbreviated in the figure so that it will fit on one line). Property 3 shows the maximum file size—20MB by default (rather than the Blackbox trace's 5MB). Property 4 shows the stop time: NULL indicates that no stop time has been set. Property 5 indicates whether the trace is currently running (a value of 1 means that it is).
By examining the \MSSQL\LOG directory after using my new SQL Server 2005 system for a few days, I could tell that SQL Server started a new trace file each time I restarted the system. Therefore, I had a record of information from before the most recent startup of SQL Server. It seems that SQL Server will store as many as five versions of the log file, numbering the files sequentially and removing the oldest file as new ones are created. After several days of stopping and starting my SQL Server system multiple times, I had five trace files, named log_7.trc, log_8.trc, and so forth, with the most recent file being log_11.trc. I still haven't determined whether the filenames have a maximum threshold, after which the names would be recycled.
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.
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.