October 20, 2014 at 9:08 am
Comments posted to this topic are about the item Querying deadlock graph from SQL Extended Events
October 23, 2014 at 1:52 am
Hi Marcelo,
thank you for sharing this script, for a long time I was looking for it!
Until now I had not found how to get the data in a deadlock after it happened.
Gualtiero
October 23, 2014 at 4:48 am
Thanks for the feedback.
I'm glad it may be usefull for you.
October 30, 2014 at 10:29 am
With our DBA we tried to speed up the query using tmp table.
On our server the time change from 1 minutes to 6 seconds.
Here below the query:
DECLARE @dt date = '20141016';
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL
) ) AS Id ,
CAST(DL.value('.[1]', 'varchar(max)') AS XML) AS Graph
INTO #Deadlock
FROM ( SELECT CAST(st.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 s.name = 'system_health'
AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY Data.TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]/child::node()')
AS X1 ( DL )
WHERE @dt IS NULL
OR DL.value('../@timestamp', 'date') = @dt
SELECT d.Id ,
V.value('(@id)[1]', 'varchar(50)') AS VictimId
INTO #Victim
FROM #Deadlock d
CROSS APPLY d.Graph.nodes('deadlock/victim-list/victimProcess') AS X2 ( V )
SELECT d.Id ,
P.value('(@id)[1]', 'varchar(50)') AS ProcessId ,
P.value('(@spid)[1]', 'int') AS Spid ,
P.value('(@clientapp)[1]', 'varchar(200)') AS AppName ,
P.value('(@loginname)[1]', 'varchar(50)') AS LoginName ,
P.value('(@hostname)[1]', 'varchar(50)') AS HostName ,
P.value('(inputbuf)[1]', 'varchar(max)') AS Command
INTO #Process
FROM #Deadlock d
CROSS APPLY d.Graph.nodes('deadlock/process-list/process') AS X3 ( P )
SELECT d.Id ,
ROW_NUMBER() OVER ( PARTITION BY d.id ORDER BY ( SELECT
NULL
) ) AS ResId ,
R.value('(@associatedObjectId)[1]', 'bigint') AS HobtId ,
R.value('(@dbid)[1]', 'int') AS DbId ,
R.value('(@objectname)[1]', 'sysname') AS ObjName ,
R.value('(@indexname)[1]', 'sysname') AS IdxName ,
R.query('owner-list/owner') AS OwnerList ,
R.query('waiter-list/waiter') AS WaiterList ,
R.value('local-name(.)', 'varchar(15)') AS LockLevel
INTO #Resource
FROM #Deadlock d
CROSS APPLY d.Graph.nodes('deadlock/resource-list/child::node()')
AS X4 ( R )
SELECT r.Id ,
r.ResId ,
RO.value('(@id)[1]', 'varchar(50)') AS OwnerProcessId ,
RO.value('(@mode)[1]', 'varchar(3)') AS OwnerLockMode
INTO #ResOwner
FROM #Resource r
CROSS APPLY r.OwnerList.nodes('owner') AS X5 ( RO )
SELECT r.Id ,
r.ResId ,
RW.value('(@id)[1]', 'varchar(50)') AS WaiterProcessId ,
RW.value('(@mode)[1]', 'varchar(3)') AS WaiterLockMode
INTO #ResWaiter
FROM #Resource r
CROSS APPLY r.WaiterList.nodes('waiter') AS X6 ( RW )
SELECT DB_NAME(r.DbId) AS DbName ,
r.ObjName ,
r.IdxName ,
r.HobtId ,
r.LockLevel ,
vp.Spid AS VictimSpid ,
vp.AppName AS VictimAppName ,
vp.HostName AS VictimHostName ,
vp.LoginName AS VictimLoginName ,
vp.Command AS VictimCommand ,
rw.WaiterLockMode AS VictimLockMode ,
op.Spid AS WinnerSpid ,
op.AppName AS WinnerAppName ,
op.HostName AS WinnerHostName ,
op.LoginName AS WinnerLoginName ,
op.Command AS WinnerCommand ,
ro.OwnerLockMode AS WinnerLockMode ,
CAST(d.Graph AS VARCHAR(MAX)) Graph
INTO #tmp_deadlocks
FROM #Deadlock d
INNER JOIN #Victim v ON v.Id = d.Id
INNER JOIN #Process vp ON vp.Id = d.Id
AND vp.ProcessId = v.VictimId
INNER JOIN #ResWaiter rw ON rw.Id = d.Id
AND rw.WaiterProcessId = v.VictimId
INNER JOIN #Resource r ON r.Id = d.Id
AND r.ResId = rw.ResId
INNER JOIN #ResOwner ro ON ro.Id = d.Id
AND ro.ResId = rw.ResId
INNER JOIN #Process op ON op.Id = d.Id
AND op.ProcessId = ro.OwnerProcessId
ORDER BY d.Id
DROP TABLE #Deadlock
DROP TABLE #Victim
DROP TABLE #Process
DROP TABLE #ResWaiter
DROP TABLE #Resource
DROP TABLE #ResOwner
SELECT t.DbName ,
T.ObjName ,
t.IdxName ,
t.HobtId ,
t.LockLevel ,
t.VictimSpid ,
t.VictimAppName ,
t.VictimHostName ,
t.VictimLoginName ,
t.VictimCommand ,
t.VictimLockMode ,
t.WinnerSpid ,
t.WinnerAppName ,
t.WinnerHostName ,
t.WinnerLoginName ,
t.WinnerCommand ,
t.WinnerLockMode ,
CAST(t.Graph AS XML) Graph
FROM #tmp_deadlocks t
DROP TABLE #tmp_deadlocks;
We also found that it seems that not all deadlock are extracted.
Our application send to us an email when a deadlock occur (it simply catch the error returned by DB) and we found that there is no match of a today email on the result of this query.
We will try to understand why this deadlock is not extracted, did you have some suggestion?
October 30, 2014 at 12:28 pm
Hello again Gualtiero,
That's also a possible approach using temp tables instead of CTE. Many times we can speed up the query execution by splitting the query and making it less complex.
About the deadlock that is not returned by this query, 2 options come to my mind: the query or the buffer area.
- check the base DMVs directly to confirm if the events were caught. If not, it could be explained by the ring buffer area which has a limited size and might have been cycled or the service might have been restarted. If yes, we should understand why the query is filtering the result.
I executed the query this week when a deadlock event occurred and got a different scenario and the query seems not 100% correct and need some improvement.
Share with us if you find out the problem and other improvements if you do.
April 4, 2016 at 6:26 am
Hi Marcelo,
Thx, this is a useful script to include in your SQL toolkit.
One suggestion you may wish to add to your article is to check that the TRACE flags are enabled to capture deadlock graphs in XML. e.g.
DBCC TRACEON (1204, -1) -- show info about nodes involved in the deadlock
DBCC TRACEON (1222, -1) -- show deadlock info in an XML format
I found that some DBAs do not enable these settings by default - which in my view is a pain when you are asked to fix a deadlock.
Cheers
Terry
April 4, 2016 at 6:54 am
Thanks for the script.
April 4, 2016 at 9:18 am
Having never looked at deadlock issues, I find the whole script somewhat hard to digest.
May I suggest expanding the article to include a short introduction to Extended Events and how this applies to your script.
I even find the whole sequence of cross apply's overwhelming. It took 8 seconds to run on my local server SS2k12.
April 4, 2016 at 3:30 pm
Hi j-1064772,
Thanks for your reply and for your suggestion.
When I wrote this post in the past I was thinking about just sharing the script and forgot to add some references about Extended Events.
In fact, the query ended up being a little complex using CTEs and it takes longer than acceptable, but its pretty easy to convert the script to use Temp tables and it will speed up a lot, as commented earlier.
Also, I found an issue running this query on SQL 2012+ and I had to edit a little bit (don't remember if it was due to a xml format change).
I'll find the new version, tune it a little bit, add some references and edit the post.
Thanks.
September 19, 2016 at 7:29 am
It was good re-reading it.
September 21, 2016 at 2:19 am
There is a problem with the script with 2008 R2(SP3). I tried to modify script using some tips and trick for replacing xml graph, but without results. Do you know, perhaps, what seems to be the trouble?
September 21, 2016 at 2:21 am
There is a problem with the script with 2008 R2(SP3). I tried to modify script using some tips and trick for replacing xml graph, but without results. Do you know, perhaps, what seems to be the trouble?
September 21, 2016 at 4:31 pm
Hi Darko,
It's been a long time I wrote this code and run on SQL 2008 R2. I don't even have any instance to test.
Run the main select on the ring buffer DMV and share with us the XML, then I can try to help you to understand the reason it's not working.
September 22, 2016 at 12:11 am
Hi,
Thank you for responding.
I slightly modified your script in order to receive an e-mail when deadlock occured.
Yesterday, I accidentally browse through server log ( tf 1204 and 1222 are on ) and noticed following information. I made an export part of log, so some part of log ( severity ) is marked as unknown.
There is no doubt it was deadlock.
Here is the log
Date,Source,Severity,Message
09/22/2016 00:00:36,spid14s,Unknown,This instance of SQL Server has been using a process ID of 1468 since 9.11.2015. 10:06:48 (local) 9.11.2015. 9:06:48 (UTC). This is an informational message only; no user action is required.
09/21/2016 13:41:23,spid23s,Unknown,waiter id=process937708 mode=IX requestType=convert
09/21/2016 13:41:23,spid23s,Unknown,waiter-list
09/21/2016 13:41:23,spid23s,Unknown,owner id=process91d288 mode=S
09/21/2016 13:41:23,spid23s,Unknown,owner-list
09/21/2016 13:41:23,spid23s,Unknown,pagelock fileid=1 pageid=2577111 dbid=8 objectname=INF_FK.MEDF_0.STAC_Prijem id=lock24895eb80 mode=SIU associatedObjectId=72057597090988032
09/21/2016 13:41:23,spid23s,Unknown,waiter id=process91d288 mode=S requestType=wait
09/21/2016 13:41:23,spid23s,Unknown,waiter-list
09/21/2016 13:41:23,spid23s,Unknown,owner id=process937708 mode=SIX
09/21/2016 13:41:23,spid23s,Unknown,owner-list
09/21/2016 13:41:23,spid23s,Unknown,pagelock fileid=1 pageid=2559186 dbid=8 objectname=INF_FK.MEDF_0.STAC_Prijem_Racuni id=lock20e850c80 mode=SIX associatedObjectId=72057597285367808
09/21/2016 13:41:23,spid23s,Unknown,resource-list
09/21/2016 13:41:23,spid23s,Unknown,UPDATE MEDF_0.STAC_PRIJEM SET <nl/> IZNOS_UCESCA=(SELECT TOP 1 IZNOS_UCESCA FROM MEDF_0.STAC_RACUNI R LEFT OUTER JOIN MEDF_0.STAC_PRIJEM_RACUNI PR ON PR.ID_RACUNA=R.ID WHERE ID_PRIJEMA= MEDF_0.STAC_PRIJEM.ID ORDER BY R.ID DESC)<c/> <nl/> IZNOS_DOPUNSKOG=(SELECT TOP 1 IZNOS_DOPUNSKOG FROM MEDF_0.STAC_RACUNI R LEFT OUTER JOIN MEDF_0.STAC_PRIJEM_RACUNI PR ON PR.ID_RACUNA=R.ID WHERE ID_PRIJEMA=MEDF_0.STAC_PRIJEM.ID ORDER BY R.ID DESC)<c/><nl/> IZNOS_DZO_DOPUNSKOG=(SELECT TOP 1 IZNOS_DZO_DOPUNSKOG FROM MEDF_0.STAC_RACUNI R LEFT OUTER JOIN MEDF_0.STAC_PRIJEM_RACUNI PR ON PR.ID_RACUNA=R.ID WHERE ID_PRIJEMA=MEDF_0.STAC_PRIJEM.ID ORDER BY R.ID DESC)<c/><nl/> IZNOS_ZA_NAPLATU=(SELECT TOP 1 IZNOS_ZA_NAPLATU FROM MEDF_0.STAC_RACUNI R LEFT OUTER JOIN MEDF_0.STAC_PRIJEM_RACUNI PR ON PR.ID_RACUNA=R.ID WHERE ID_PRIJEMA=MEDF_0.STAC_PRIJEM.ID ORDER BY
09/21/2016 13:41:23,spid23s,Unknown,inputbuf
09/21/2016 13:41:23,spid23s,Unknown,UPDATE MEDF_0.STAC_PRIJEM SET <nl/> IZNOS_UCESCA=(SELECT TOP 1 IZNOS_UCESCA FROM MEDF_0.STAC_RACUNI R LEFT OUTER JOIN MEDF_0.STAC_PRIJEM_RACUNI PR ON PR.ID_RACUNA=R.ID WHERE ID_PRIJEMA= MEDF_0.STAC_PRIJEM.ID ORDER BY R.ID DESC)<c/> <nl/> IZNOS_DOPUNSKOG=(SELECT TOP 1 IZNOS_DOPUNSKOG FROM MEDF_0.STAC_RACUNI R LEFT OUTER JOIN MEDF_0.STAC_PRIJEM_RACUNI PR ON PR.ID_RACUNA=R.ID WHERE ID_PRIJEMA=MEDF_0.STAC_PRIJEM.ID ORDER BY R.ID DESC)<c/><nl/> IZNOS_DZO_DOPUNSKOG=(SELECT TOP 1 IZNOS_DZO_DOPUNSKOG FROM MEDF_0.STAC_RACUNI R LEFT OUTER JOIN MEDF_0.STAC_PRIJEM_RACUNI PR ON PR.ID_RACUNA=R.ID WHERE ID_PRIJEMA=MEDF_0.STAC_PRIJEM.ID ORDER BY R.ID DESC)<c/><nl/> IZNOS_ZA_NAPLATU=(SELECT TOP 1 IZNOS_ZA_NAPLATU FROM MEDF_0.STAC_RACUNI R LEFT OUTER JOIN MEDF_0.STAC_PRIJEM_RACUNI PR ON PR.ID_RACUNA=R.ID WHERE ID_PRIJEMA=MEDF_0.STAC_PRIJEM.ID ORDER BY R.ID DESC)
09/21/2016 13:41:23,spid23s,Unknown,frame procname=adhoc line=2 stmtstart=82 sqlhandle=0x02000000a2f0750832c114918fab869d677cf3d1006d1f81
09/21/2016 13:41:23,spid23s,Unknown,executionStack
09/21/2016 13:41:23,spid23s,Unknown,process id=process937708 taskpriority=0 logused=851104 waitresource=PAGE: 8:1:2577111 waittime=4840 ownerId=2163948994 transactionname=user_transaction lasttranstarted=2016-09-21T13:40:07.663 XDES=0x2c83783b0 lockMode=IX schedulerid=8 kpid=7148 status=suspended spid=120 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-09-21T13:41:19.050 lastbatchcompleted=2016-09-21T13:41:19.047 clientapp=MEDF\Petra Lozar(petra) hostname=PLOZAR hostpid=948 loginname=iraapp isolationlevel=read committed (2) xactid=2163948994 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056
09/21/2016 13:41:23,spid23s,Unknown,SELECT TOP 100 <nl/> MEDF_0.STAC_PRIJEM.*<c/> COMMONZDR.PACIJENTI.IME<c/> COMMONZDR.PACIJENTI.PREZIME<c/><nl/> RTRIM(COMMONZDR.PACIJENTI.PREZIME) + ' ' + RTRIM(COMMONZDR.PACIJENTI.IME) PACIJENT<c/><nl/> COMMONZDR.PACIJENTI.DATUM_RODENJA<c/> COMMONZDR.PACIJENTI.OIB<c/> COMMONZDR.PACIJENTI.HZZO_BROJ MBO_PACIJENTA<c/><nl/> COMMONZDR.PACIJENTI.SPOL<c/> COMMONZDR.PACIJENTI.JMBG<c/> COMMONZDR.PACIJENTI.IMEOCA<c/> COMMONZDR.PACIJENTI.IMEMAJKE<c/><nl/> COMMON.GRADOVI.NAZIV NAZIV_GRADA<c/> COMMONZDR.PODRUCNIUREDI.NAZIV NAZIV_PU<c/><nl/> COMMON.DRZAVE.NAZIV NAZIV_DRZAVE<c/> COMMONZDR.OSNOVE.NAZIV NAZIV_OSNOVE<c/><nl/> COMMONZDR.DZOSIGURAVATELJI.NAZIV NAZIV_DZOSIG<c/> ODJEL_PRIJEM.NAZIV NAZIV_ODJELA<c/> ODJEL_OTPUST.NAZIV NAZIV_ODJELA_OTPUSTA<c/><nl/> COMMONZDR.STAC_KATEGORIJE_BOLNICKOG_RACUNA.NAZIV NAZIV_KATEGORIJE_BR<c/><nl/> COMMONZDR.STAC_NACIN_PRIJEMA_S.NAZIV NAZIV_NACINA_PRIJEMA<c/> COMMONZDR.UPUTNEUSTANOVE.NAZIV NAZIV_UPUTUSTANOVE<c/><nl/> COMMONZDR.OZNAKE_UPUCIVANJA.NAZIV NAZIV_OZNAKEUPUCIVANJA<c/>
09/21/2016 13:41:23,spid23s,Unknown,inputbuf
09/21/2016 13:41:23,spid23s,Unknown,SELECT TOP 100 <nl/> MEDF_0.STAC_PRIJEM.*<c/> COMMONZDR.PACIJENTI.IME<c/> COMMONZDR.PACIJENTI.PREZIME<c/><nl/> RTRIM(COMMONZDR.PACIJENTI.PREZIME) + ' ' + RTRIM(COMMONZDR.PACIJENTI.IME) PACIJENT<c/><nl/> COMMONZDR.PACIJENTI.DATUM_RODENJA<c/> COMMONZDR.PACIJENTI.OIB<c/> COMMONZDR.PACIJENTI.HZZO_BROJ MBO_PACIJENTA<c/><nl/> COMMONZDR.PACIJENTI.SPOL<c/> COMMONZDR.PACIJENTI.JMBG<c/> COMMONZDR.PACIJENTI.IMEOCA<c/> COMMONZDR.PACIJENTI.IMEMAJKE<c/><nl/> COMMON.GRADOVI.NAZIV NAZIV_GRADA<c/> COMMONZDR.PODRUCNIUREDI.NAZIV NAZIV_PU<c/><nl/> COMMON.DRZAVE.NAZIV NAZIV_DRZAVE<c/> COMMONZDR.OSNOVE.NAZIV NAZIV_OSNOVE<c/><nl/> COMMONZDR.DZOSIGURAVATELJI.NAZIV NAZIV_DZOSIG<c/> ODJEL_PRIJEM.NAZIV NAZIV_ODJELA<c/> ODJEL_OTPUST.NAZIV NAZIV_ODJELA_OTPUSTA<c/><nl/> COMMONZDR.STAC_KATEGORIJE_BOLNICKOG_RACUNA.NAZIV NAZIV_KATEGORIJE_BR<c/><nl/> COMMONZDR.STAC_NACIN_PRIJEMA_S.NAZIV NAZIV_NACINA_PRIJEMA<c/> COMMONZDR.UPUTNEUSTANOVE.NAZIV NAZIV_UPUTUSTANOVE<c/><nl/> COMMONZDR.OZNAKE_UPUCIVANJA.NAZIV NAZIV_OZNAKEUPUCIVANJA<c/>
09/21/2016 13:41:23,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006a3832107ee62d3d27155306e192f0a779d37e42
09/21/2016 13:41:23,spid23s,Unknown,executionStack
09/21/2016 13:41:23,spid23s,Unknown,process id=process91d288 taskpriority=0 logused=0 waitresource=PAGE: 8:1:2559186 waittime=8672 ownerId=2163962190 transactionname=SELECT lasttranstarted=2016-09-21T13:41:14.753 XDES=0x535801cb0 lockMode=S schedulerid=7 kpid=7076 status=suspended spid=137 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2016-09-21T13:41:14.753 lastbatchcompleted=2016-09-21T13:41:14.750 clientapp=MEDF\Mario Krcelic(mkrcelic) hostname=ARHIVA01 hostpid=5544 loginname=iraapp isolationlevel=read committed (2) xactid=2163962190 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056
09/21/2016 13:41:23,spid23s,Unknown,process-list
09/21/2016 13:41:23,spid23s,Unknown,deadlock victim=process91d288
09/21/2016 13:41:23,spid23s,Unknown,deadlock-list
09/21/2016 13:41:23,spid137,Unknown,Transaction (Process ID 137) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
09/21/2016 13:41:23,spid137,Unknown,Error: 1205<c/> Severity: 13<c/> State: 52.
09/21/2016 13:41:23,spid6s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000535801CB0 Mode: S SPID:137 BatchID:0 ECID:0 TaskProxy:(0x00000005EDA0A540) Value:0x5b1d00 Cost:(0/0)
09/21/2016 13:41:23,spid6s,Unknown,Victim Resource Owner:
09/21/2016 13:41:23,spid6s,Unknown,
09/21/2016 13:41:23,spid6s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000002C83783B0 Mode: IX SPID:120 BatchID:0 ECID:0 TaskProxy:(0x00000004DA4BE540) Value:0xe6c7d700 Cost:(0/851104)
09/21/2016 13:41:23,spid6s,Unknown,Requested by:
09/21/2016 13:41:23,spid6s,Unknown,Input Buf: Language Event: SELECT TOP 100 <nl/> MEDF_0.STAC_PRIJEM.*<c/> COMMONZDR.PACIJENTI.IME<c/> COMMONZDR.PACIJENTI.PREZIME<c/><nl/> RTRIM(COMMONZDR.PACIJENTI.PREZIME) + ' ' + RTRIM(COMMONZDR.PACIJENTI.IME) PACIJENT<c/><nl/> COMMONZDR.PACIJENTI.DATUM_RODENJA<c/> COMMONZ
09/21/2016 13:41:23,spid6s,Unknown,SPID: 137 ECID: 0 Statement Type: SELECT Line #: 1
09/21/2016 13:41:23,spid6s,Unknown,Owner:0x00000005BEB8BC40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:137 ECID:0 XactLockInfo: 0x0000000535801CF0
09/21/2016 13:41:23,spid6s,Unknown,Grant List 3:
09/21/2016 13:41:23,spid6s,Unknown,Grant List 1:
09/21/2016 13:41:23,spid6s,Unknown,PAGE: 8:1:2577111 CleanCnt:2 Mode:SIU Flags: 0x3
09/21/2016 13:41:23,spid6s,Unknown,Node:2
09/21/2016 13:41:23,spid6s,Unknown,
09/21/2016 13:41:23,spid6s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000535801CB0 Mode: S SPID:137 BatchID:0 ECID:0 TaskProxy:(0x00000005EDA0A540) Value:0x5b1d00 Cost:(0/0)
09/21/2016 13:41:23,spid6s,Unknown,Requested by:
09/21/2016 13:41:23,spid6s,Unknown,Input Buf: Language Event: <nl/> UPDATE MEDF_0.STAC_PRIJEM SET <nl/> IZNOS_UCESCA=(SELECT TOP 1 IZNOS_UCESCA FROM MEDF_0.STAC_RACUNI R LEFT OUTER JOIN MEDF_0.STAC_PRIJEM_RACUNI PR ON PR.ID_RACUNA=R.ID WHERE ID_
09/21/2016 13:41:23,spid6s,Unknown,SPID: 120 ECID: 0 Statement Type: UPDATE Line #: 2
09/21/2016 13:41:23,spid6s,Unknown,Owner:0x00000005CD711C00 Mode: SIX Flg:0x40 Ref:0 Life:02000000 SPID:120 ECID:0 XactLockInfo: 0x00000002C83783F0
09/21/2016 13:41:23,spid6s,Unknown,Grant List 1:
09/21/2016 13:41:23,spid6s,Unknown,PAGE: 8:1:2559186 CleanCnt:2 Mode:SIX Flags: 0x3
09/21/2016 13:41:23,spid6s,Unknown,Node:1
09/21/2016 13:41:23,spid6s,Unknown,
09/21/2016 13:41:23,spid6s,Unknown,Wait-for graph
09/21/2016 13:41:23,spid6s,Unknown,Deadlock encountered .... Printing deadlock information
As you suggest I run following query, but without results
E.g. there is not rows.
DECLARE @dt date = '20160921'
;
WITH Deadlock
AS
(SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT
NULL)
) AS Id
,CAST(DL.value('.[1]', 'varchar(max)') AS XML) AS Graph
FROM (SELECT
CAST(st.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 s.name = 'system_health'
AND st.target_name = 'ring_buffer') AS Data
CROSS APPLY Data.TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]/child::node()') AS X1 (DL)
WHERE @dt IS NULL
OR DL.value('../@timestamp', 'date') = @dt)
SELECT
*
FROM DEADLOCK;
What seems to be the trouble?
Thanks in advance
September 22, 2016 at 6:09 am
It seems that there is a DELAY in writing information about deadlock. I do not know why. In server 2012 everything works fine. But in server version 2008R2, I am able to see deadlock information only two days ago.
I noticed today that information about deadlock on 20.9.2016 is written today?!
Is there any configuration parametar which handles these things? Does service broker should be enabled?
Thanks in advance
D.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply