The Default Trace is something completely new that Microsoft has implemented to audit certain events in the system, which you can report on via Management Reports. To verify if the default trace is running, run following query:
select * from sys.configurations where configuration_id = 1568
In order to check whether you have active traces runnig, execute:select * from ::fn_trace_getinfo(0)
For more information on trace properties and server side traces see fn_trace_getinfo and sp_trace_create in BOL.
If you are concerned about these traces, you can turn them off by running following:
sp_configure 'default trace enabled', 0
But should you turn it off? Before you make that determination, take a closer look at what is being captured. By opening the log file in the new Profiler, you can see in the event matrix exactly what is being captured. Below is a list of the events the default trace is capturing. It appears that all columns available for the trace events are being captured.
Errors and Warnings
This trace seems to be fairly light-weight. Some of the auditing alerts are triggered from this trace, as well, in addition to the extremely cool, Management Reports dependence on these traces. BOL states, that "Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur." Also if you turn off the trace, you won't have the option to debug stored procedures from Management Studio 2005.
PingBack from http://www.keyongtech.com/2242905-log_nnn-trc-who-runs-this
am currently working in sql 2008 profiler. in 2005 i can see Audit broker login event but where as not present in the 2008. why is not there? i can see some of the event which are there in 2005 but not in 2008