SQL Server – Deadlock diagnosis in SQL Server – A very quick guide to locate problem

Do You have problems with deadlocks on SQL Server or on Portal Azure? Do You want locate problem in quick time? Below I present very quick guide how in the easiest way perform analyse without additional or sophisticated tools. In case of deadlocs, problem not bases on fixing problematic sql statements but on finding source of problem. I love simple solutions and below I present step by step solution with pure sql statement:
1. Please open sql command window on Master DB – Very important: NOT on DB where is problem
2. Paste following sql statemnt to sql command window

WITH deadlockcte 
     AS (SELECT Cast(event_data AS XML) AS [target_data_XML] 
         FROM   sys.Fn_xe_telemetry_blob_target_read_file('dl', NULL, NULL, NULL 
                )) 
SELECT 
target_data_xml.value('(/event/@timestamp)[1]', 'DateTime2') 
AS Timestamp, 
target_data_xml.query('/event/data[@name="xml_report"]/value/deadlock') 
AS deadlock_xml, 
target_data_xml.query('/event/data[@name="database_name"]/value').value('(/value)[1]', 'nvarchar(100)') AS db_name 
FROM   deadlockcte

3. Execute sql statement and as result You should see below similar result


4. When You click on deadlock_xml You will see operations responsible for deadlocks on Your DB 🙂

 

September 12th, 2017

  • My spouse and i were really fulfilled that Emmanuel could deal with his researching by way of the precious recommendations he grabbed when using the site. It’s not at all simplistic just to find yourself giving freely tips which usually many people could have been trying to sell. And we also fully grasp we’ve got you to appreciate for this. Those explanations you made, the easy website menu, the relationships your site make it possible to engender – it’s got many superb, and it’s really leading our son in addition to us imagine that that topic is cool, which is tremendously serious. Thank you for the whole lot!.

  • Thanks for your entire efforts on this blog. Betty really likes going through research and it’s really obvious why. A number of us notice all about the lively means you give advantageous tricks through the web site and as well boost participation from other individuals about this area of interest while our own simple princess is truly studying a whole lot. Take pleasure in the remaining portion of the new year. You have been carrying out a stunning job..

  • Leave a Reply

    Your email address will not be published. Required fields are marked *