Default Trace in SQL Server 2005

  • Article

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.

Database

  • Data file auto grow
  • Data file auto shrink
  • Database mirroring status change
  • Log file auto grow
  • Log file auto shrink

Errors and Warnings

  • Errorlog
  • Hash warning
  • Missing Column Statistics
  • Missing Join Predicate
  • Sort Warning

Full-Text

  • FT Crawl Aborted
  • FT Crawl Started
  • FT Crawl Stopped

Objects

  • Object Altered
  • Object Created
  • Object Deleted

Security Audit

  • Audit Add DB user event
  • Audit Add login to server role event
  • Audit Add Member to DB role event
  • Audit Add Role event
  • Audit Add login event
  • Audit Backup/Restore event
  • Audit Change Database owner
  • Audit DBCC event
  • Audit Database Scope GDR event (Grant, Deny, Revoke)
  • Audit Login Change Property event
  • Audit Login Failed
  • Audit Login GDR event
  • Audit Schema Object GDR event
  • Audit Schema Object Take Ownership
  • Audit Server Starts and Stops

Server

  • Server Memory Change

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.