DBA Scripts: Get Active XE Sessions

Extended Events sessions have a way of quietly accumulating on a busy instance. Someone stood up a trace for a specific investigation six months ago, and it’s still running, still writing files, and nobody remembers why. Before you start a new session, or clean up an old one, you need one honest answer to “what’s actually running right now, and where is it writing.”

Two scripts answer that here, and they answer different halves of it. The first is the inventory: every non-default Extended Events session currently active, its target, its output file, and whether it is set to survive a service restart. The second is the bill: what those sessions are costing the instance in buffers, dropped events, and blocked firing time.

Why Active XE Sessions Matters

Extended Events is low overhead per session, but sessions left running indefinitely still cost something: disk space filling up with rolling .xel files nobody’s reading, buffer memory held for events nobody’s consuming, and the simple operational risk of not knowing what’s collecting data on your server:

• A session created for a one-off investigation and never removed keeps consuming disk and buffer resources long after anyone needs the data.
• startup_state decides whether a session survives a service restart. A session you thought was temporary can turn out to be permanent if it was created with STARTUP_STATE = ON.
• Dropped events or dropped buffers on a running session are an early sign it’s under more load than its configuration can keep up with.

When to Run This Script

• Before creating a new Extended Events session, to check whether a similar or overlapping session is already running.
• As part of a routine health check, to catch sessions abandoned after past investigations.
• Immediately after starting one of the trace sessions in this series, to confirm it is running and writing to the file path you expect.
• Before a cleanup pass with Remove XE Session, to see what is active before deciding what to stop.

The Scripts

Run these against your SQL Server instance. The first tells you what exists. The second tells you what it costs.

1. Get-ActiveXeSessions — What Is Running, and Where Its Files Are

/*
Script Name : Get-ActiveXeSessions
Category    : traces
Purpose     : Shows all currently running Extended Events sessions with their targets and file output paths.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-active-xe-sessions/)
Requires    : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    s.name AS session_name,
    s.create_time AS started_at,
    DATEDIFF(HOUR, s.create_time, GETDATE()) AS running_hours,
    t.target_name,
    CAST(
        CAST(t.target_data AS XML).value(
            '(EventFileTarget/File/@name)[1]', 'nvarchar(500)')
    AS NVARCHAR(500)) AS output_file,
    s.total_buffer_size / 1024 / 1024 AS buffer_size_mb,
    s.dropped_event_count,
    s.dropped_buffer_count,
    ses.startup_state AS auto_start_on_restart
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t ON t.event_session_address = s.address
JOIN sys.server_event_sessions ses ON ses.name = s.name
WHERE s.name NOT IN (
    'system_health', 'telemetry_xevents', 'hkenginexesession',
    'AlwaysOn_health', 'sp_server_diagnostics session'
)
ORDER BY s.create_time DESC;

Reads sys.dm_xe_sessions joined to its running targets and sys.server_event_sessions, excludes the built-in system sessions (system_health, telemetry_xevents, hkenginexesession, AlwaysOn_health, sp_server_diagnostics session), and returns every DBA-created session that’s currently active, newest first.

2. Get-ExtendedEventsSessions — What Those Sessions Are Costing

The inventory tells you a session exists. This one tells you whether it is paying its way: memory held in buffers, events dropped because those buffers could not keep up, and time the server spent blocked while the session fired. It is part of the standard health check for that reason.

/*
Script Name : Get-ExtendedEventsSessions
Category    : monitoring
Purpose     : Active Extended Events sessions — name, state, targets, and estimated disk impact.
              Surfaces unexpected or high-overhead XE sessions on inherited servers.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-active-xe-sessions/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

SELECT
    s.name AS session_name,
    s.create_time,
    CASE s.pending_buffers WHEN 0 THEN 'ACTIVE' ELSE 'ACTIVE (pending writes)' END AS state,
    s.total_regular_buffers AS buffer_count,
    s.regular_buffer_size AS buffer_size_bytes,
    s.total_large_buffers AS large_buffer_count,
    s.large_buffer_size AS large_buffer_size_bytes,
    s.dropped_event_count AS dropped_events,
    s.dropped_buffer_count AS dropped_buffers,
    s.blocked_event_fire_time AS blocked_fire_time_ms,
    STUFF((
        SELECT ', ' + t.target_name
        FROM sys.dm_xe_session_targets t
        WHERE t.event_session_address = s.address
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(500)'), 1, 2, '') AS targets,
    /* Reads the counters above. The old version keyed on target type and looked for
       asynchronous_file_target, the SQL Server 2008 name: since 2012 the file target is
       event_file, so file sessions always read OK. Blocked firing only happens when the
       session is set to EVENT_RETENTION_MODE = NO_EVENT_LOSS. */
    CASE
        WHEN s.blocked_event_fire_time > 0
            THEN 'Blocking the workload - events waited for buffer space (NO_EVENT_LOSS); allow event loss or capture less'
        WHEN s.dropped_event_count > 0 OR s.dropped_buffer_count > 0
            THEN 'Losing events - buffers cannot keep up; raise MAX_MEMORY or filter the events'
        ELSE 'OK'
    END AS overhead_note,
    CASE s.name
        WHEN 'system_health' THEN 'Built-in - monitors deadlocks, connectivity errors, scheduler health'
        WHEN 'AlwaysOn_health' THEN 'Built-in - AG health events (present on AG instances)'
        WHEN 'telemetry_xevents' THEN 'Built-in - SQL Server telemetry collection'
        WHEN 'hkenginexesession' THEN 'Built-in - In-Memory OLTP (Hekaton) session'
        WHEN 'sp_server_diagnostics session'
                                      THEN 'Built-in - WSFC diagnostics for AG/FCI'
        ELSE 'Custom or third-party session - verify purpose and owner'
    END AS session_note
FROM sys.dm_xe_sessions AS s
ORDER BY
    CASE WHEN s.name IN ('system_health','AlwaysOn_health','telemetry_xevents',
                          'hkenginexesession','sp_server_diagnostics session')
         THEN 1 ELSE 0 END,
    s.name;

Same source DMVs, a different question: this reports buffer allocation and the two loss counters per session, then labels the overhead so a session that is quietly hurting the instance is obvious without reading the numbers. One scope difference worth knowing: unlike the inventory above, this one includes SQL Server’s own sessions, so expect system_health and telemetry_xevents in the results.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# List every non-default Extended Events session currently running:
.\run.ps1 Get-ActiveXeSessions

# To run against a remote sql server:
.\run.ps1 Get-ActiveXeSessions -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/traces/Get-ActiveXeSessions.sql
• powershell/wrappers/traces/Get-ActiveXeSessions.ps1

Example Output

1. What is running. 3 sessions, all created by a DBA, none of them SQL Server’s own. Every output_file shows the real name on disk, with the sequence and timestamp suffix SQL Server appends, which is the path to hand to the read-back script.

2. What it costs. The same instance through the overhead script, and note the row count: 7, not 3. This one does not filter out SQL Server’s own sessions, so system_health, telemetry_xevents and friends appear alongside yours. That is deliberate. Every counter reads 0 here and every overhead_note reads OK. If a built-in such as system_health shows dropped events, that is not an alarm: the built-ins drop events routinely under load and are not yours to tune. Read the loss counters against the sessions you created.

Understanding the Results

Best Practices

• Run this before starting any new session: a name collision or an overlapping capture wastes effort and disk space.
• Treat a session with a running_hours count in the thousands as a question, not a fact. Find out why it’s still running before assuming it’s fine.
• Watch dropped_event_count/dropped_buffer_count on anything you’re relying on for an audit or decommission decision. Dropped data undermines the “we saw nothing” conclusion you’re trying to prove.
• Pair this with Remove XE Session as a routine 2-step check: see what is running here, then decide what to clean up there.

Microsoft’s reference covers sys.dm_xe_sessions, sys.server_event_sessions and sp_server_diagnostics in full.

Related Scripts

You may also find these scripts useful:

• Create Decommission Audit Session
• Create Login Activity Session
• Create SP Execution Session
• Get XE Session Activity
• Remove XE Session
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

A running Extended Events session is easy to start and easy to forget. This script is the honest inventory, what’s actually collecting data on this instance right now, where it’s writing, and whether it survives a restart, so a trace never becomes a permanent fixture nobody remembers creating.

Run it before starting anything new, and make it a routine check alongside cleanup with Remove XE Session.