Determining current diagnostic tracing settings (SQL)

You can retrieve the diagnostic tracing settings in effect by querying the sa_diagnostic_tracing_level table.

Prerequisites

DBA or PROFILE authority.

Context and remarks

Many.

Connect to the database.
Query the sa_diagnostic_tracing_level table for rows in which the enabled column contains a 1.

Results

The database server returns the diagnostic tracing settings currently in use. A 1 in the enabled column indicates that the setting is in effect.

Next

None.

Example

The following statement shows you how to query the sa_diagnostic_tracing_level diagnostic table to retrieve the current diagnostic tracing settings:

SELECT * FROM sa_diagnostic_tracing_level WHERE enabled = 1;

The following table is an example result set from the query:

id	scope	identifier	trace_type	trace_condition	value	enabled
1	database	(NULL)	volatile_statistics	sample_every	1,000	1
2	database	(NULL)	nonvolatile_statistics	sample_every	60.000	1
3	database	(NULL)	connection_statistics	(NULL)	60,000	1
4	database	(NULL)	blocking	(NULL)	(NULL)	1
5	database	(NULL)	deadlock	(NULL)	(NULL)	1
6	database	(NULL)	plans_with_statistics	sample_every	2,000	1