C# deadlock information

Question

Is there a good way to report on Microsoft SQL server deadlock information in C#? I have a C# program that is running queries, updates, etc... Occasionally it gets a deadlock error but the error message is not particularly helpful in diagnosing the problem:

Transaction (Process ID 347) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Right now I have to track down a DBA to go search through SQL logs to get the deadlock graph. I was wondering if there is a way to get at this information programatically?

you can try something like this http://stackoverflow.com/questions/13159326/implementing-retry-logic-for-deadlock-exceptions — NinjaDeveloper, Jun 28 '16 at 12:30
See if it helps to you: http://stackoverflow.com/questions/320636/how-to-get-efficient-sql-server-deadlock-handling-in-c-sharp-with-ado — Abhay Dixit, Jun 28 '16 at 13:48

score 0 · Answer 1 · answered Jun 28 '16 at 12:33

The details of the deadlock are not returned to the client. In later versions of SQL Server, one method to get deadlock details programmatically is by querying the system_health Extended Event session like the sample query below, which you can tweak for your specific situation. Note that this method requires VIEW SERVER STATE permissions.

--get deadlock_report from ring_buffer target
SELECT
       xed.value('@timestamp', 'datetime') as Creation_Date,
       xed.query('.') AS Extend_Event
FROM
(
       SELECT CAST([target_data] AS XML) AS Target_Data
       FROM sys.dm_xe_session_targets AS xt
       INNER JOIN sys.dm_xe_sessions AS xs
       ON xs.address = xt.event_session_address
       WHERE xs.name = N'system_health'
       AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC;
GO

C# deadlock information

1 Answers1