Retrouver les deadlock qui se sont produits sur une instance SQL Serveur 2008
Par François le vendredi, février 11 2011, 16:32 - SQL Server - Lien permanent
Alors il y a quelques temps je me suis demander si c'était possible de retrouver des deadlocks qui se seraient produit sur une base de données SQL Serveur 2005 (Microsoft). Je me suis dit : "peut être avec un outil qui irait chercher ça dans le journal de transaction (fichier LDF) ou quelque chose comme ça". Mais j'ai pas trouvé par contre je suis tombé sur un lien : apparemment avec SQL Server 2008, il est désormais possible avec une requête SQL de lister tous les deadlocks d'une instance SQL.
Voici le lien : http://blog.developpez.com/elsuket/p9234/moteur-de-base-de-donnees-sql-server/obtenir-la-liste-des-deadlocks-retrospec/
Ca reste à approfondir (pas eu le temps), par exemple, est ce que cette liste est limitée au X derniers deadlocks ?
Voici le script (récupéré sur le lien) :
;WITH -- Récupération des documents XML capturés par la session xml_event_payload AS ( SELECT CAST(T.target_data AS xml) AS data FROM sys.dm_xe_session_targets AS T INNER JOIN sys.dm_xe_sessions AS S ON S.address = T.event_session_address WHERE S.name = 'system_health' ), prepare AS -- Extraction des événements de deadlock ( SELECT ED.event_xml.value('(./data/value)[1]', 'varchar(max)') AS deadlock_data FROM xml_event_payload AS XEP CROSS APPLY data.nodes('/RingBufferTarget/event') AS ED (event_xml) WHERE ED.event_xml.value('./@name', 'varchar(32)') = 'xml_deadlock_report' ), well_form_xml_doc AS -- Formation correcte du document XML (mal formé par défaut) ( SELECT REPLACE(REPLACE(deadlock_data, '<victim-list>', '<deadlock><victim-list>'), '<process-list>','</victim-list><process-list>') AS deadlock_data FROM prepare ), del_victim_list AS -- Formation correcte du document XML (mal formé par défaut) ( SELECT CAST(STUFF(deadlock_data, PATINDEX('%<victim-list>%', deadlock_data), PATINDEX('%</victim-list>%', deadlock_data) + 14 - PATINDEX('%<victim-list>%', deadlock_data), '') AS xml) AS deadlock_data , ROW_NUMBER() OVER(ORDER BY deadlock_data) AS n FROM well_form_xml_doc ) SELECT n , D.name AS database_name , DC.deadlock_characteristics.value('./@lasttranstarted', 'datetime') AS occurence_date_time , DC.deadlock_characteristics.value('(./inputbuf)[1]', 'varchar(max)') AS query , deadlock_data FROM del_victim_list CROSS APPLY deadlock_data.nodes('/deadlock-list/deadlock/process-list/process') AS DC (deadlock_characteristics) INNER JOIN sys.databases AS D ON D.database_id = DC.deadlock_characteristics.value('./@currentdb', 'int') WHERE DC.deadlock_characteristics.value('./@lasttranstarted', 'datetime') > '20100823 00:00:00.000' ORDER BY del_victim_list.n