1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
| --Get Deadlock from XEvents
SELECT CONVERT(xml, event_data).query('/event/data/value/child::*'),
CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\system_health*.xel', null, null, null)
WHERE object_name like 'xml_deadlock_report'
Sinon par ici : https://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx
Il est dans system_health
La colonne name, chercher xml_deadlock_report
En haut à gauche, cliquer sur Filters et filtrer sur name et xml_deadlock_report
--http://blog.developpez.com/elsuket/p12987/moteur-de-base-de-donnees-sql-server/recuperer-les-graphes-des-deadlocks-retrospectivement-les-requetes-adequates-aux-versions
--SQL Server 2008 R2
SELECT occurence_date_time
, CAST(T.event_data.value('(event/data/value)[1]', 'varchar(max)') AS xml) AS deadlock_graph
, GETDATE() AS local_date_time
FROM (
SELECT XDR.xdr.query('.') AS event_data
, XDR.xdr.value('@timestamp', 'datetime') AS occurence_date_time
FROM (
-- Cast the target_data to XML
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets AS ST
JOIN sys.dm_xe_sessions AS S
ON s.address = st.event_session_address
WHERE S.name = 'system_health'
AND ST.target_name = 'ring_buffer'
) AS TD -- Split out the Event Nodes
CROSS APPLY TD.target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XDR(xdr)
) AS T(event_data, occurence_date_time)
ORDER BY occurence_date_time DESC
-- SQL Server 2012, 2014, 2016
SELECT SRC.occurence_date_time
, SRC.deadlock_graph.query('(event/data/value/deadlock)[1]') AS deadlock_graph
, GETDATE() AS local_date_time
FROM (
SELECT XDR.xdr.query('.') AS deadlock_graph
, XDR.xdr.value('@timestamp', 'datetime') AS occurence_date_time
FROM (
SELECT CAST(ST.target_data AS xml) AS target_data
FROM sys.dm_xe_session_targets AS ST
INNER JOIN sys.dm_xe_sessions AS S
ON S.address = ST.event_session_address
WHERE S.name = 'system_health'
AND ST.target_name = 'ring_buffer'
) AS TD(target_data)
CROSS APPLY TD.target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XDR(xdr)
) AS SRC
ORDER BY SRC.occurence_date_time DESC;
Une des query dans le lien plus haut https://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx ,donne moins de résultat que celle de Nicolas:
DECLARE @version int
SET @version = (@@microsoftversion / 0x1000000) & 0xff;
IF (@version = 10)
BEGIN
WITH SystemHealth
AS (
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = 'system_health'
AND st.target_name = 'ring_buffer')
SELECT XEventData.XEvent.value('(data/value)[1]','VARCHAR(MAX)') AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'
END
IF (@version > 10)
BEGIN
WITH SystemHealth
AS (
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = 'system_health'
AND st.target_name = 'ring_buffer')
SELECT XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'
END |
