dutyDBA.com

Have you got too many Extended Events sessions on your SQL Servers, accumulated over a period of time? Not sure which of those are running? and which of those are auto starting at server startup? Looking for a way to stop unnecessary extended sessions, and control the duration of your extended events sessions? You’ve come to the right place. In this post, I am going to show you how to check what sessions are currently running, when they started, are they auto starting. I will also show you how to stop or drop those sessions easily and will introduce you to a new feature introduced in SQL Server 2025 that limits the duration of Extended Events sessions.

What Extended Events sessions are currently running on your SQL Server?

Whilst you can see a graphical representation of your Extended Events sessions under the Management > Extended Events > Sessions node of SQL Server Management Studio (SSMS), the below query gives you a better view of all your sessions:

SELECT
	ses.name AS SessionName,
	CASE
		WHEN xs.name IS NOT NULL 
			THEN 'Yes'
			ELSE 'No'
	END AS isRunning,
	CASE
		WHEN xs.name IS NOT NULL 
			THEN 'ALTER EVENT SESSION ' + QUOTENAME(ses.name) + ' ON SERVER STATE = STOP'
			ELSE ''
	END AS CommandToStopSession,
	xs.create_time AS XE_SessionStartTime,
	CASE ses.startup_state 
		WHEN 1 
			THEN 'Yes' 
			ELSE 'No' 
	END AS isAutoStarting,
	CASE ses.startup_state
		WHEN 1 
			THEN 'ALTER EVENT SESSION '+ QUOTENAME(xs.name) + ' ON SERVER WITH (STARTUP_STATE=OFF)'
			ELSE ''
	END AS [CommandToTurnOffAutoStart],
	'DROP EVENT SESSION ' + QUOTENAME(ses.name) + ' ON SERVER' AS CommandToDropSession
FROM
	sys.server_event_sessions AS ses
	LEFT JOIN
	sys.dm_xe_sessions AS xs
	ON	xs.name = ses.name AND 
		  xs.session_source = 'server'
WHERE ses.name NOT IN ('system_health', 'AlwaysOn_health')

SELECT
	ses.name AS SessionName,
	CASE
		WHEN xs.name IS NOT NULL 
			THEN 'Yes'
			ELSE 'No'
	END AS isRunning,
	CASE
		WHEN xs.name IS NOT NULL 
			THEN 'ALTER EVENT SESSION ' + QUOTENAME(ses.name) + ' ON SERVER STATE = STOP'
			ELSE ''
	END AS CommandToStopSession,
	xs.create_time AS XE_SessionStartTime,
	CASE ses.startup_state 
		WHEN 1 
			THEN 'Yes' 
			ELSE 'No' 
	END AS isAutoStarting,
	CASE ses.startup_state
		WHEN 1 
			THEN 'ALTER EVENT SESSION '+ QUOTENAME(xs.name) + ' ON SERVER WITH (STARTUP_STATE=OFF)'
			ELSE ''
	END AS [CommandToTurnOffAutoStart],
	'DROP EVENT SESSION ' + QUOTENAME(ses.name) + ' ON SERVER' AS CommandToDropSession
FROM
	sys.server_event_sessions AS ses
	LEFT JOIN
	sys.dm_xe_sessions AS xs
	ON	xs.name = ses.name AND 
		  xs.session_source = 'server'
WHERE ses.name NOT IN ('system_health', 'AlwaysOn_health')

The output shows you the following:

• Names of all your Extended Events sessions
• Whether they are currently running or not
• The command to stop the session if its currently running
• The start time of the session
• Is it set to auto start when SQL Server service starts
• The command to stop the session from auto starting at server startup
• The command to drop the session

Review the output and decide whether a session should be running or not, and whether a session should be auto starting at SQL Server startup or not. If you want to either stop or drop a session, or change the start up property, simply grab the command from the output and execute it in the query window.

Note: The above query does not show any internal and system Extended Events sessions like sp_server_diagnostics session and hkenginexesession.

It also omits the built-in sessions like system_health and AlwaysOn_health. Its better to let them run.

DBAs often set up new Extended Events sessions to troubleshoot a specific issue, but forget to drop them after resolving the issue. Sometimes when working on a support case with Microsoft engineers, DBAs set up new Extended Events sessions, and they get forgotten about. Its important to stop any unnecessary Extended Events sessions to prevent wastage of server resources and generattion of large amounts of output

Want to set up a new Extended Events session, but only for a specific duration. How to set an end time for a session?

Prior to SQL Server 2025, it wasn’t possible to automatically stop an Extended Events session after a set amount of time. If you wanted to start a session and run it for a duration of 4 hours, for example – you had to have another job that ran after 4 hours to stop that Extended Events session.

Basically you either manually or though a different process/job, had to execute the below command to stop a session at a specified time. There simply wasn’t a way to specify an end time for the session:

ALTER EVENT SESSION [Trace Deadlocks] ON SERVER STATE = STOP

ALTER EVENT SESSION [Trace Deadlocks] ON SERVER STATE = STOP

What’s changed in SQL Server 2025? Is there a way to run an Extended Events session for a specific duration?

Yes is the answer! SQL Server 2025 has introduced time-bound Extended Event sessions – that stop automatically after a specified time limit elapses. This is a very welcome new feature.

You can set a time limit for a session via the CREATE EVENT SESSION or ALTER EVENT SESSION using the MAX_DURATION argument. SQL Server guarantess to stop your session once it reaches the specified maximum duration, and prevents sessions from running indefinitely.

MAX_DURATION can be specified in either SECONDS, MINUTES, HOURS or DAYS, up to a maximum duration of 24 days.

Here’s an example session that automatically stops after an hour:

CREATE EVENT SESSION [Audit Logins] ON SERVER 
ADD EVENT sqlserver.login
(
	ACTION
	(
		sqlserver.server_principal_name,
		sqlserver.client_hostname,
		sqlserver.client_app_name
	)
)
ADD TARGET package0.event_file(SET filename=N'C:\TraceData\XE_LoginAudit.XEL', max_file_size=(100))
WITH 
(
	MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
	MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF, 
	MAX_DURATION = 60 MINUTES
)

CREATE EVENT SESSION [Audit Logins] ON SERVER 
ADD EVENT sqlserver.login
(
	ACTION
	(
		sqlserver.server_principal_name,
		sqlserver.client_hostname,
		sqlserver.client_app_name
	)
)
ADD TARGET package0.event_file(SET filename=N'C:\TraceData\XE_LoginAudit.XEL', max_file_size=(100))
WITH 
(
	MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
	MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF, 
	MAX_DURATION = 60 MINUTES
)

Example output:

Hope the query at the beginning of this post helps you make sense of your Extended Events sessions. Give it go and share your finfings by leaving a comment below!