The other day I was troubleshooting a production system with an issue reported by the application team. As per the application team they were getting disconnects reported in the logs and wanted this to be investigated.

I put up an Extended Events session similar to the one given below and was able to precisely identify the aborts that were occurring and was able to provide details around the same.

In the XE script below, I have captured the following two events –

  1. sqlserver.attention
  2. sqlserver.rpc_completed

Also, an important thing to note is the additional filter that I have put along with the rpc_completed event. The system I was working on was very chatty and was a critical production system. To ensure that not every RPC call gets captured I filtered the calls exceeding the duration of 1000000 microseconds i.e., 1000 milliseconds. You might want to tweak this as per your environment.

CREATE EVENT SESSION [DBA_CaptureAbortedQuery] ON SERVER 
ADD 
  EVENT sqlserver.attention(
    ACTION(
      sqlserver.client_app_name, sqlserver.client_hostname, 
      sqlserver.database_id, sqlserver.database_name, 
      sqlserver.session_id, sqlserver.sql_text, 
      sqlserver.username
    ) 
    WHERE 
      (
        [package0].[equal_boolean](
          [sqlserver].[is_system], 
          (0)
        )
      )
  ), 
ADD 
  EVENT sqlserver.rpc_completed(
    SET 
      collect_data_stream =(1) ACTION(
        sqlserver.database_id, sqlserver.database_name, 
        sqlserver.server_principal_name, 
        sqlserver.session_id
      ) 
    WHERE 
      (
        [sqlserver].[database_id] =(7) 
        AND [package0].[equal_boolean](
          [sqlserver].[is_system], 
          (0)
        ) 
        AND [package0].[greater_than_max_uint64](
          [duration], 
          (1000000)
        )
      )
  ) 
ADD 
  TARGET package0.event_file(
    SET 
      filename = N'D:\XELogs\DBA_PerfCapture_CaptureAbortedQuery.xel', 
      max_file_size =(100), 
      max_rollover_files =(20)
  ) 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 = ON, STARTUP_STATE = OFF
  ) 
GO