Extended Events sessions stops collection when edited while the session is on run
0
votes
3
answers
121
views
I created an Extended Events session - based on Standard SQL template.
Just added Global filter on sqlserver.databasename for a particular DB.
Started watching live data.
Updated the session to edit it and added few more events. And clicked ok. The session was running during the edit happened.
Now the Watch live data stopped adding rows.
I did refresh the 'sessions' folder + the created session. But the Watch Live Data didn't resume.
Closed and opened again by right clicking the session name and chose 'Watch Live Data'. Now it shows 'Retrieving event information from server' but never adds up any events.
When checked the Target Data from eventfile. The last time it traced is before the edit happened. Though a day passed, the Target data never gets updated.
Have to create new session.
Does Extended Event session functions so?
Or Am I wrong? Kindly help. Can't keep creating new session for every edit we need.
Thank you!
Adding screenshot from Standard XEvent Profiler which is too not working.
Here is the script of the session - (Changed session name & DB name alone)
CREATE EVENT SESSION [SessionName] ON SERVER
ADD EVENT sqlserver.database_xml_deadlock_report(
ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([sqlserver].[database_name]=N'DBName')),
ADD EVENT sqlserver.existing_connection(SET collect_database_name=(1),collect_options_text=(1)
ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([sqlserver].[database_name]=N'DBName')),
ADD EVENT sqlserver.lock_deadlock_chain(SET collect_database_name=(1),collect_resource_description=(1)
ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)),
ADD EVENT sqlserver.rpc_starting(SET collect_data_stream=(1)
ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'DBName')),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([sqlserver].[database_name]=N'DBName')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([sqlserver].[database_name]=N'DBName')),
ADD EVENT sqlserver.sql_batch_starting(
ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'DBName')),
ADD EVENT sqlserver.sql_statement_completed(SET collect_parameterized_plan_handle=(1)
ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([sqlserver].[database_name]=N'DBName'))
ADD TARGET package0.event_file(SET filename=N'E:\SessionName.xel')
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
Event global filter which I mentioned is as below - to apply in all events


Asked by Meera K
(81 rep)
Aug 23, 2024, 07:08 AM
Last activity: Apr 11, 2025, 11:26 AM
Last activity: Apr 11, 2025, 11:26 AM