DBA Scripts: Get Active XE Sessions

Part of the DBA-Tools Project.

---

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.”

This script gives you exactly that: every non-default Extended Events session currently active, its target, its output file, and whether it’s set to survive a service restart.

---

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 determines 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 similarly-named or overlapping session is already running
• As part of a routine health check, to catch abandoned sessions from past investigations
• Immediately after starting one of the trace sessions in this series, to confirm it’s actually running and pointed at the file path you expect
• Before a cleanup pass with Remove XE Session, to see what’s currently active before deciding what to stop

---

The Script

Run the following script against your SQL Server instance.

/*
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.

---

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

session_name	started_at	running_hours	target_name	buffer_size_mb	dropped_event_count	dropped_buffer_count	auto_start_on_restart
SpExecution	24/07/2026 21:34:16	0	event_file	4	0	0	True
DecommissionAudit	24/07/2026 21:34:07	0	event_file	4	0	0	True
LoginActivity	24/07/2026 21:33:53	0	event_file	4	0	0	True

Real output captured against a local SQL Server 2025 instance, right after standing up all three trace sessions from this series with their own Create scripts. output_file is trimmed from this table (it’s a long, timestamp-suffixed path), each one resolved to something like D:\SQLTrace\SpExecution_0_134293988560260000.xel, the base name plus a sequence number and file-creation timestamp that SQL Server appends automatically.

---

Understanding the Results

• running_hours at 0 here just means these sessions were seconds old when captured. On a real instance, a session that’s been running for months is either intentional and monitored, or forgotten, this column is how you tell them apart.
• dropped_event_count and dropped_buffer_count should both stay at 0. Non-zero values mean the session’s buffer configuration can’t keep up with event volume and data is being lost, worth investigating (or increasing max_file_size/buffer sizing) rather than trusting the output as complete.
• auto_start_on_restart reflects STARTUP_STATE. True means this session comes back automatically after a service restart, useful for something you want permanent, a trap if you thought it was temporary.
• output_file tells you exactly where to point Get XE Session Activity or SSMS’s own Extended Events viewer to read the captured data back.

---

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 two-step check: see what’s running here, then decide what to clean up there.

---

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
• Extended Events Sessions (built-in + custom, explicitly labeled)

---

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.