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 
target_data_xml.value('(/event/@timestamp)[1]', 'DateTime2') 
AS Timestamp, 
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..

  • Thank you a lot for giving everyone an exceptionally nice chance to read critical reviews from this site. It is often so brilliant and also full of a lot of fun for me and my office peers to visit your blog at the very least thrice every week to read through the latest items you have. And definitely, I am also usually contented considering the powerful creative concepts you give. Selected 2 tips in this article are in fact the most suitable we’ve had..

  • I was more than happy to seek out this internet-site.I wanted to thanks for your time for this wonderful read!! I definitely enjoying every little little bit of it and I’ve you bookmarked to check out new stuff you weblog post..

  • Aw, this was a really nice post. In thought I want to put in writing like this additionally ?taking time and precise effort to make a very good article?but what can I say?I procrastinate alot and certainly not seem to get one thing done..