Querying deadlock graph from SQL Extended Events

  • Comments posted to this topic are about the item Querying deadlock graph from SQL Extended Events

  • 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

  • Thanks for the feedback.

    I'm glad it may be usefull for you.

  • 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?

  • 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.

  • 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

  • Thanks for the script.

  • 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.

  • 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.

  • It was good re-reading it.

  • 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?

  • 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?

  • 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.

  • 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

  • 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