Sql-server – Extended Event definition information for a stopped session

extended-eventssql serversql server 2014

I am trying to get configuration information, via T-SQL, about existing, stopped extended-event sessions. If the session is started, then the sys.dm_xe_session_object_columns DMV gives me exactly what I'm looking for. Is that information available somewhere for stopped sessions?

Best Answer

Extended event catalog views fall under both sys.dm_xe* and sys.server_event_* views. The "active" or "running" event sessions have information stored in sys.dm_xe* (also global package metadata) views and overall server defined metadata stored in sys.server_event_* views regardless of state.

Specifically since the question was about information on extended event sessions that aren't currently running, the metadata for that still exists and the basics of it can be found in sys.server_event_sessions catalog view. Joining this with other catalog views such as sys.server_event_session_events, sys.server_event_session_actions, and sys.server_event_session_targets (to name a few) can return most of the information that should be needed.

Related Solutions

Sql-server – Collecting parameter values from Extended event session

Someone mentioned "I think only sqlserver.rpc_completed event will give you complete SQL command including parameter values." Please review answer below:

Capture Passed Parameter Values In Sql Server Extended Sessions

Sql-server – SQL Server 2012 extended event session to expose the network protocol

Found the answer.

The login event is correct and the network protocol is exposed via the options_text action. options_text was always blank for me previously and this needs to be set on via SET collect_options_text=(1). An example session might be: -

CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(
    sqlserver.session_id
    ,sqlserver.username
    ))
ADD TARGET package0.ring_buffer
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
)
GO

And this would then populate options_text with something like: -

network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed

I found this out be chance by creating a session from the "Connection Tracking" Microsoft shipped extended events template.

That template includes "SET collect_options_text=(1)" and I wasn't previously aware such an option existed.

An example is also given in this sql-server-performance article. The last screenshot shows options_text populated with "network protocol: LPC.." LPC is shared memory.

Best Answer

Related Solutions

Sql-server – Collecting parameter values from Extended event session

Sql-server – SQL Server 2012 extended event session to expose the network protocol

Related Question