Event tracing

Event tracing records information about system-defined and user-defined trace events to an event trace data (ETD) file.

A trace session is made up of trace events (specific points in the database server software or your SQL application) that collect information that is logged to a target. Targets are the location (such as a file) where the database server logs trace events.

The information that is logged during an event tracing session includes the information from the specified system- and user-defined trace events. Event tracing can be used to diagnose database server and application issues, including performance issues, on production databases.

System trace events
These are trace events that the database server generates. Trace events are generated for operations such as starting or ending a checkpoint and starting or stopping a database. You can query the available system events and system event fields by using the sp_trace_events and sp_trace_event_fields system procedures. Event fields contain pertinent information about the event. System events are not maintained when you rebuild a database, and the set of system events that is available depends on the version of the database server. System trace events can change across major releases.

Audit events are a subset of system trace events and log information to the database's audit log. Query the available audit events and audit event fields by using the sp_trace_events and sp_trace_event_fields system procedures. However, you must have the MANAGE AUDITING system privilege.

Note The audit events do not directly correspond to available values of the types parameter in the sa_enable_auditing type system procedure. When you enable auditing by using the sa_enable_auditing_type system procedure, specify what type of information to audit. The actual auditing events are system events generated by the database server.
User trace events
These trace events log information from an application to an event tracing session. User trace events are visible to all connections to the database. You can create a user trace event by using the CREATE TEMPORARY TRACE EVENT statement.

Event tracing targets

The only supported event tracing target is an event trace data (ETD) file. The ETD file is platform independent and can be processed with the dbmanageetd utility. You can also retrieve data from an ETD file using the sp_read_etd system procedure.

Trace sessions

A trace session is a collection of trace events and targets for a database. Trace sessions are visible to all connections to the database, and trace sessions persist until the database is shut down.

Event severities

The following SQL severity levels are defined for all trace event types:

Level	Severity value range
ALWAYS	0
CRITICAL	1-50
ERROR	51-100
WARNING	101-150
INFORMATION	151-200
DEBUG	201-255

Setting up event tracing for a database

Perform the following steps to set up event tracing for a database:

Create user trace events within your application for the information you want included in the diagnostic log.
Create a procedure that calls the NOTIFY TRACE EVENT statement.
Create a trace session and add trace events to the session.
Start the event tracing session.