CODEWITHSUNDEEP
sql-serverextended-events
Juozas
Juozas

Reputation: 935

SQL Server extended events: track exception with SP call

I need to track failed remote procedure calls (RPC started/completed events).

What I tried:

But, I cant find way how to track both in one place.

P.S. I cannot use output params to return error and track it using RPC completed event.

Upvotes: 0

Views: 1480

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

If you add the TRACK_CAUSALITY option on the event session, it will add an action that allows you to correlate events that were generated by the same thing happening. Here's a sample event session I just made:

CREATE EVENT SESSION [sp errors] ON SERVER 
ADD EVENT sqlserver.error_reported,
ADD EVENT sqlserver.module_end(SET collect_statement=(1)),
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1))
ADD TARGET package0.ring_buffer
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

I then created a stored procedure with a divide-by-zero error and called it. Here's what I got in the ring buffer (truncated to the relevant events):

<event name="error_reported" package="sqlserver" timestamp="2018-01-26T15:44:58.652Z">
    <data name="error_number">
      <type name="int32" package="package0"></type>
      <value>8134</value>
    </data>
    <data name="severity">
      <type name="int32" package="package0"></type>
      <value>16</value>
    </data>
    <data name="state">
      <type name="int32" package="package0"></type>
      <value>1</value>
    </data>
    <data name="user_defined">
      <type name="boolean" package="package0"></type>
      <value>false</value>
    </data>
    <data name="category">
      <type name="error_category" package="sqlserver"></type>
      <value>2</value>
      <text><![CDATA[SERVER]]></text>
    </data>
    <data name="destination">
      <type name="error_destination" package="sqlserver"></type>
      <value>0x00000002</value>
      <text><![CDATA[USER]]></text>
    </data>
    <data name="is_intercepted">
      <type name="boolean" package="package0"></type>
      <value>false</value>
    </data>
    <data name="message">
      <type name="unicode_string" package="package0"></type>
      <value><![CDATA[Divide by zero error encountered.]]></value>
    </data>
    <action name="attach_activity_id_xfer" package="package0">
      <type name="activity_id_xfer" package="package0"></type>
      <value>110CE72A-3B30-4086-BD7E-9E2E3734C803-0</value>
    </action>
    <action name="attach_activity_id" package="package0">
      <type name="activity_id" package="package0"></type>
      <value>A3050AC2-36EF-493F-9E37-BF7884EF55DC-1</value>
    </action>
  </event>
  <event name="module_end" package="sqlserver" timestamp="2018-01-26T15:44:58.657Z">
    <data name="source_database_id">
      <type name="uint32" package="package0"></type>
      <value>2</value>
    </data>
    <data name="object_id">
      <type name="int32" package="package0"></type>
      <value>-1530582118</value>
    </data>
    <data name="duration">
      <type name="uint64" package="package0"></type>
      <value>6025</value>
    </data>
    <data name="row_count">
      <type name="uint64" package="package0"></type>
      <value>0</value>
    </data>
    <data name="line_number">
      <type name="int32" package="package0"></type>
      <value>1</value>
    </data>
    <data name="offset">
      <type name="int32" package="package0"></type>
      <value>0</value>
    </data>
    <data name="offset_end">
      <type name="int32" package="package0"></type>
      <value>-1</value>
    </data>
    <data name="object_type">
      <type name="ansi_string" package="package0"></type>
      <value><![CDATA[P ]]></value>
    </data>
    <data name="object_name">
      <type name="unicode_string" package="package0"></type>
      <value><![CDATA[#foo____________________________________________________________________________________________________________________00005935]]></value>
    </data>
    <data name="statement">
      <type name="xml" package="package0"></type>
      <value>exec #foo</value>
    </data>
    <action name="attach_activity_id" package="package0">
      <type name="activity_id" package="package0"></type>
      <value>A3050AC2-36EF-493F-9E37-BF7884EF55DC-2</value>
    </action>
  </event>

Each of those events has an attach_activity_id node with mostly the same value. The value is the same if you cast it as a uniqueidentifier, though!

Godspeed. You're going to need it for the XML shredding that's in your future. :)

Upvotes: 2

Related Questions