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.
Prev. page
[1]
2
next page 
