DBA Scripts: Get Extended Events Sessions

---

What’s Actually Running on a Server You Just Inherited

Extended Events sessions are easy to lose track of. Someone sets one up to chase a specific problem, the problem gets fixed, and the session keeps running quietly forever. On a server you’ve just inherited, you have no idea what’s active, who created it, or whether it’s a built-in session SQL Server needs or a custom one someone forgot about.

Get Active XE Sessions answers a related but narrower question, it filters out the built-ins entirely to show only custom sessions someone set up, with the output file path. This script shows everything, built-in and custom side by side, with each one explicitly labeled, useful specifically when you don’t yet know which session names are the expected built-ins and which aren’t.

This script lists every active session, what it targets, and whether it’s a recognized built-in or something that needs a closer look.

---

Why Extended Events Session Visibility Matters

• A forgotten custom session with a ring_buffer or file target that nobody’s reading is pure overhead with no one benefiting from it
• Built-in sessions (system_health, AlwaysOn_health, telemetry_xevents, hkenginexesession, the WSFC diagnostics session) are expected and should be left alone, distinguishing them from custom sessions matters
• Dropped events or dropped buffers on a session mean it’s not capturing everything it’s configured to, a quiet data-loss problem for whoever depends on that session’s output
• File and ring buffer targets carry real I/O and memory cost, worth knowing about on an inherited server before assuming everything active is harmless

---

When to Run This Script

• Any time you inherit a server you haven’t administered before
• Investigating unexpected memory or I/O overhead with no obvious query-level cause
• Routine health checks, to catch a diagnostic session that outlived its original purpose
• Before adding a new Extended Events session, to check whether something similar already exists

---

The Script

/*
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-extended-events-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,
    CASE
        WHEN EXISTS (
            SELECT 1 FROM sys.dm_xe_session_targets t
            WHERE t.event_session_address = s.address
              AND t.target_name IN ('asynchronous_file_target', 'ring_buffer')
        ) THEN 'File/ring buffer — potential I/O or memory overhead'
        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;

The session_note column is what makes this scan through an inherited server fast: five names are recognized as built-in and everything else is flagged for a closer look, rather than making you memorize which session names are expected.

---

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 active Extended Events session, built-in and custom:
.\run.ps1 Get-ExtendedEventsSessions

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

This script lives in the repo at:

• sql/monitoring/features/Get-ExtendedEventsSessions.sql

---

Example Output

session_name	create_time	targets	overhead_note	session_note
LoginActivity	2026-07-26 19:14:00	event_file	OK	Custom or third-party session, verify purpose and owner
sp_server_diagnostics session	2026-07-23 15:45:31	router	OK	Built-in, WSFC diagnostics for AG/FCI
hkenginexesession	2026-07-23 15:45:30	event_file	OK	Built-in, In-Memory OLTP (Hekaton) session
system_health	2026-07-23 15:45:31	ring_buffer, event_file	File/ring buffer, potential I/O or memory overhead	Built-in, monitors deadlocks, connectivity errors, scheduler health
telemetry_xevents	2026-07-27 16:34:36	ring_buffer	File/ring buffer, potential I/O or memory overhead	Built-in, SQL Server telemetry collection

The genuinely useful row here is LoginActivity, a custom session set up earlier this session to trace login activity, correctly flagged as needing its purpose verified rather than silently ignored, exactly what this script is for on a server someone else configured.

---

Understanding the Results

• session_note = “Custom or third-party session” — this is the row to actually investigate on an inherited server; find out who created it, why, and whether it’s still needed
• overhead_note flags a file or ring buffer target — both carry real cost, a ring buffer holds events in memory, a file target writes to disk continuously
• dropped_events or dropped_buffers > 0 — the session isn’t capturing everything configured, usually because event throughput exceeds what the target can absorb; increasing buffer size or reducing event volume are the two fixes
• A session missing entirely that you expected — check whether it was ever created at all, or whether it’s stopped rather than dropped (a stopped session doesn’t appear in sys.dm_xe_sessions, only active ones do)

---

Best Practices

• Run this early when inheriting any server, unexplained custom sessions are a common surprise
• Don’t drop a session you don’t recognize without checking first, it may be feeding a dashboard or alert someone else depends on
• Re-run after setting up any new session to confirm it started as expected and isn’t already dropping events
• Treat system_health and other built-ins as expected background cost, not something to tune down without a specific reason

---

Related Scripts

You may also find these scripts useful:

• Extended Events Tracing (hub)
• Get Active XE Sessions, the narrower custom-sessions-only view referenced above
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

An inherited server accumulates Extended Events sessions the same way it accumulates everything else nobody documented, one at a time, for reasons that made sense once and are now forgotten. This script separates the built-in sessions SQL Server needs from everything else, so the custom sessions actually worth a closer look don’t get lost in the noise.

Run it early on any server you’re getting to know, and treat an unrecognized custom session as a question to answer, not a detail to ignore.