CODEWITHSUNDEEP
sql-serverextended-events
ElRoro
ElRoro

Reputation: 213

Understand login logout events on SQL Server extended events after upgrading compatibility level

After upgrading the compatibility level of our SQL Server to 150, we're facing some random issues getting the SCOPE_IDENTITY after an INSERT. To find the issue, we create an event session like this :

CREATE EVENT SESSION [QuickSessionTSQL] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
    WHERE ([severity]>=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902) OR [error_number]=(41354) OR [error_number]=(41355) OR [error_number]=(41367) OR [error_number]=(41384) OR [error_number]=(41336) OR [error_number]=(41309) OR [error_number]=(41312) OR [error_number]=(41313)))),
ADD EVENT sqlserver.existing_connection(
    ACTION(package0.event_sequence,sqlserver.client_hostname,sqlserver.session_id)),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(package0.event_sequence,sqlserver.client_hostname,sqlserver.session_id)),
ADD EVENT sqlserver.logout(
    ACTION(package0.event_sequence,sqlserver.session_id)),
ADD EVENT sqlserver.rpc_starting(
    ACTION(package0.event_sequence,sqlserver.database_name,sqlserver.session_id)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(package0.event_sequence,sqlserver.database_name,sqlserver.session_id)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))
WITH (MAX_MEMORY=16384 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=OFF)
GO

And we see our INSERT statements and the SELECT SCOPE_IDENTITY but with strange behavior. Depending on the sqlserver.session_id, we notice one logout event and one login event after each SQL statement. Whereas on the other sqlserver.session_id we just see the usual statements.

First scenario (the good one) :

The second scenario (the bad one) :

There is a different behavior depending on the session, but why? And what means the lot of login/logout events in this case?

Microsoft SQL Server 2019 (KB4583458) - 15.0.2080.9 (X64)

Upvotes: 0

Views: 366

Answers (0)

Related Questions