Sql-server – Extended Event Session Keeps Stopping

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

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.

SSMS | Management | Extended Events | Sessions | Right click | New session | General page | Template = Connection Tracking.

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.