Quick Scan Report – Server Side Trace Running

What this check looks for

Rows in sys.traces where is_default = 0, meaning a trace somebody started rather than the default trace SQL Server runs on its own. The check is skipped on Amazon RDS.

The default trace is deliberately excluded. It is lightweight, it is on by default, and a separate check reports when it has been switched off.

Why it matters

Almost every trace on a production instance is one somebody forgot to stop.

A trace is started to investigate something, the investigation finishes, and the trace keeps running. It produces no error, it appears in no list anybody reviews, and it survives until the next service restart. Some have been running for months.

The cost is real and it depends on how it was set up:

• A trace filtered to a rare event costs very little. This is the case people remember, and it is why traces have a reputation for being cheap.
• An unfiltered trace capturing statement level events is very expensive. Every statement on the instance generates an event, and the overhead is paid by the session that ran the statement. SQL:StmtCompleted and SP:StmtCompleted on a busy OLTP instance can be a double digit percentage of throughput.
• A trace writing to a file on a data drive competes with the database for I/O and quietly fills the drive.
• A trace streamed to a client, which is what SQL Server Profiler does, is the worst case. Events are delivered synchronously, so if the client cannot keep up, SQL Server waits for it. A Profiler window on a laptop over a slow link becomes a bottleneck for the whole instance.

There is also a security dimension worth a moment: a trace capturing statement text is capturing the parameter values in that text, and the file it writes may be readable by more people than the data it contains.

How to confirm it yourself

SELECT [id],
       [is_default],
       [status],              -- 0 stopped, 1 running
       [path],
       [max_size],
       [max_files],
       [start_time],
       [last_event_time],
       [event_count],
       [dropped_event_count]
  FROM sys.traces WITH (NOLOCK)
 ORDER BY [is_default], [id];

dropped_event_count above zero means the trace could not keep up, which is a direct measurement of it being too expensive.

What each user trace is actually capturing, which decides how much it costs:

SELECT t.[id]        AS [trace_id],
       e.[name]      AS [event_name],
       c.[name]      AS [column_name]
  FROM sys.traces AS t WITH (NOLOCK)
 CROSS APPLY sys.fn_trace_geteventinfo(t.[id]) AS ei
 INNER JOIN sys.trace_events AS e WITH (NOLOCK)  ON e.[trace_event_id]  = ei.[eventid]
 INNER JOIN sys.trace_columns AS c WITH (NOLOCK) ON c.[trace_column_id] = ei.[columnid]
 WHERE t.[is_default] = 0
 ORDER BY t.[id], e.[name], c.[name];

A trace including SQL:StmtCompleted, SP:StmtCompleted, SQL:BatchStarting or RPC:Starting with no filter is the expensive kind.

How to fix it

Stop it and close it. Two statements, and both are needed: stopping leaves the trace defined, closing removes it.

EXEC sp_trace_setstatus @traceid = 2, @status = 0;   -- stop
EXEC sp_trace_setstatus @traceid = 2, @status = 2;   -- close and delete the definition

Never pass the default trace’s id, which is normally 1. Check is_default first.

Before you stop it, find out whether it is deliberate. Some third party monitoring tools and some auditing configurations run a permanent trace, and stopping it breaks them. The trace’s path usually gives it away, because the folder names the product.

Then delete the trace files, which is a separate step. They are on disk and nothing removes them.

If you need this capability again, use Extended Events. It replaced traces, it is supported on every current version, it is substantially cheaper for the same information, and it is much harder to leave running by accident because sessions are named and visible:

SELECT s.[name], s.[startup_state], st.[target_name]
  FROM sys.server_event_sessions AS s WITH (NOLOCK)
  LEFT JOIN sys.server_event_session_targets AS st WITH (NOLOCK)
         ON st.[event_session_id] = s.[event_session_id];

SQL Server Profiler and server side traces are deprecated for the database engine. Anything new should be an Extended Events session.

How long it takes

About an hour, most of it confirming that nothing depends on the trace before stopping it.

Related reports

Related checks

Frequently asked questions

Is a filtered trace really a problem? Much less of one. The check reports any user trace because it cannot judge intent, and the event list query above tells you which kind you have.

Our monitoring tool runs a trace. Then it is deliberate and the finding is informational. Note it and move on. It is still worth knowing that the tool works this way rather than using Extended Events.

Can I stop a trace someone else started? Yes, with ALTER TRACE permission. It does not matter who started it.

The trace file has grown enormous. Stop and close the trace, then delete the files. max_size and max_files control rollover, and a trace created without them just grows.