Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Catching Deadlock Information in SQL Logs


Catching Deadlock Information in SQL Logs

Author
Message
Ale Pelc
Ale Pelc
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1113 Visits: 584
Comments posted to this topic are about the item Catching Deadlock Information in SQL Logs

Alejandro Pelc
M&M
M&M
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2595 Visits: 3898
Thanks Alejandro. Good one.

M&M
lital-494950
lital-494950
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 631
by mistake i vote 4 stars but i meant it to be 5 stars.my mistake.
Thanks a lot
rama.mathanmohan
rama.mathanmohan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 164
A better way is to use SQL Server extended events in SQL2008 and the server already captures the deadlock information in the default system health check extended event. Here is the code ....

------------------------------------------------------------------- Analyse Data -------------------------------------------------------------------
SET NOCOUNT ON
set dateformat ymd

Select Min([login_time]) as 'Server Restart' from master..sysprocesses (nolock) where [lastwaittype] like '%LAZY%'

IF OBJECT_ID('tempdb..#ZZ_DeadlockEvents2008') IS NOT NULL Drop Table #ZZ_DeadlockEvents2008
IF OBJECT_ID('tempdb..#TheProcess') IS NOT NULL Drop Table #TheProcess
IF OBJECT_ID('tempdb..#TheLog') IS NOT NULL Drop Table #TheLog
IF OBJECT_ID('tempdb..#TheResource') IS NOT NULL Drop Table #TheResource
IF OBJECT_ID('tempdb..#TheAnalysis') IS NOT NULL Drop Table #TheAnalysis

Create Table #ZZ_DeadlockEvents2008([EntryNo] BIGINT IDENTITY CONSTRAINT [ZZ_DeadlockEvents2008_PK] PRIMARY KEY CLUSTERED,[Timestamp] datetime,[AlertTime] datetime,DeadlockGraph xml)
Create Table #TheLog ([Entry No] bigint CONSTRAINT [ZZ_TheLog_PK] PRIMARY KEY CLUSTERED,[DeadlockTime] datetime,DeadlockPID nvarchar(max))
Create Table #TheProcess ([Entry No] bigint,PID nvarchar(max),UserName nvarchar(max),SQL nvarchar(max),SPID int,HostName nvarchar(max))
Create Table #TheResource ([Line No] BIGINT IDENTITY CONSTRAINT [ZZ_TheResource_PK] PRIMARY KEY CLUSTERED,
[Entry No] bigint,OwnerPID nvarchar(max),WaiterPID nvarchar(max),ObjectName nvarchar(max),IndexName nvarchar(max))
Create Table #TheAnalysis ([Entry No] bigint,[DeadlockTime] datetime,DatabaseName nvarchar(max),ObjectName nvarchar(max),IndexName nvarchar(max),ObjectName2 nvarchar(max),IndexName2 nvarchar(max),
[Successful User] nvarchar(max),[Successful SPID] int,[Successful Host] nvarchar(max),[Successful SQL] nvarchar(max),
[Deadlocked User] nvarchar(max),[Deadlocked SPID] int,[Deadlocked Host] nvarchar(max),[Deadlocked SQL] nvarchar(max))

CREATE NONCLUSTERED INDEX [idx1] ON [#TheProcess] ([Entry No])
CREATE NONCLUSTERED INDEX [idx1] ON [#TheResource] ([Entry No])
CREATE NONCLUSTERED INDEX [idx1] ON [#TheAnalysis] ([Entry No])

-------------- First Workout if it is a buggy XML Graph version or not --------------

Declare @BugFlag int

Set @BugFlag = (Select Max(Case When charindex('deadlock-list',XEventData.XEvent.value('(data/value)[1]','varchar(max)')) > 0 Then 1 Else 0 End) As BugFlag
FROM
(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') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(max)') = 'xml_deadlock_report'
)
---------------------------------------------------------------------------------------

-------------- Load Row Data Into ZZ_DeadlockEvents Table --------------

If @BugFlag = 1
Begin
Insert into #ZZ_DeadlockEvents2008([Timestamp],[AlertTime],DeadlockGraph)

Select XEventData.XEvent.value('@timestamp', 'datetime') as [Timestamp]
,XEventData.XEvent.value('@timestamp', 'datetime') + cast(GETDATE() - GETUTCDATE() as time) as [DeadlockTime]
,CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
'<victim-list>', '<deadlock><victim-list>'),
'<process-list>','</victim-list><process-list>')as xml) as DeadlockGraph

FROM
(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') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(max)') = 'xml_deadlock_report'
Order By [Timestamp]
End
Else
Begin
Insert into #ZZ_DeadlockEvents2008([Timestamp],[AlertTime],DeadlockGraph)

Select XEventData.XEvent.value('@timestamp', 'datetime') as [Timestamp]
,XEventData.XEvent.value('@timestamp', 'datetime') + cast(GETDATE() - GETUTCDATE() as time) as [DeadlockTime]
,Cast(XEventData.XEvent.value('(data/value)[1]','varchar(max)') as xml) as DeadlockGraph

FROM
(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') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(max)') = 'xml_deadlock_report'
Order By [Timestamp]
End
--------- End of loading Row Data Into ZZ_DeadlockEvents Table ---------

Declare @EntryNo bigint
Declare @DeadlockTime datetime
Declare @DeadlockGraph xml
Declare @PID nvarchar(max)
Declare @Cnt int, @TotCnt int,@OwnerCnt int,@OwnerTotCnt int,@WaiterCnt int,@WaiterTotCnt int
Declare @child xml,@owner xml,@waiter xml

Declare Get_Tables CURSOR FAST_FORWARD FOR (

Select [EntryNo],[AlertTime],[DeadlockGraph] From #ZZ_DeadlockEvents2008 (nolock)
--Where [AlertTime] > '2009-04-01'

)
Open Get_Tables
FETCH NEXT FROM Get_Tables INTO @EntryNo,@DeadlockTime,@DeadlockGraph
WHILE @@FETCH_STATUS = 0
BEGIN
If @DeadlockGraph.exist('/deadlock-list/deadlock[1]') = 1
SELECT @DeadlockGraph = @DeadlockGraph.query('/deadlock-list/deadlock[1]')

Insert Into #TheLog
Select @EntryNo,@DeadlockTime,Cast(@DeadlockGraph.query('data(/deadlock/victim-list/victimProcess[1]/@id)') as NVARCHAR(MAX))

--- Insert Into List of Process ---
Select @Cnt = 1,@TotCnt = @DeadlockGraph.value('count(/deadlock/process-list/process)','INT')

WHILE @Cnt <= @TotCnt Begin
SELECT @child = @DeadlockGraph.query('deadlock/process-list/process[position()=sql:variable("@Cnt")]')

Insert Into #TheProcess
Select @EntryNo,
Cast(@child.query('data(/process[1]/@id)') as NVARCHAR(MAX)),
@child.value('(/process)[1]/@loginname', 'NVARCHAR(MAX)'),
@child.value('(/process/inputbuf)[1]', 'NVARCHAR(MAX)'),
@child.value('(/process)[1]/@spid', 'int'),
@child.value('(/process)[1]/@hostname', 'NVARCHAR(MAX)')
Select @Cnt = @Cnt + 1
End

--- Insert Into List of Resources (pagelock) ---
Select @Cnt = 1,@TotCnt = @DeadlockGraph.value('count(/deadlock/resource-list/pagelock)','INT')

WHILE @Cnt <= @TotCnt Begin
SELECT @child = @DeadlockGraph.query('/deadlock/resource-list/pagelock[position()=sql:variable("@Cnt")]')

Select @OwnerCnt = 1,@OwnerTotCnt = @child.value('count(/pagelock[1]/owner-list/owner)','INT')
WHILE @OwnerCnt <= @OwnerTotCnt Begin
Select @owner = @child.query('/pagelock[1]/owner-list/owner[position()=sql:variable("@OwnerCnt")]')

Select @WaiterCnt = 1,@WaiterTotCnt = @child.value('count(/pagelock[1]/waiter-list/waiter)','INT')
WHILE @WaiterCnt <= @WaiterTotCnt Begin

Select @waiter = @child.query('/pagelock[1]/waiter-list/waiter[position()=sql:variable("@WaiterCnt")]')

Insert Into #TheResource ([Entry No],OwnerPID,WaiterPID,ObjectName,IndexName)
Select @EntryNo,
Cast(@owner.query('data(/owner[1]/@id)') as NVARCHAR(MAX)),
Cast(@waiter.query('data(/waiter[1]/@id)') as NVARCHAR(MAX)),
@child.value('(/pagelock)[1]/@objectname', 'NVARCHAR(MAX)'),
' '

Select @WaiterCnt = @WaiterCnt + 1
End
Select @OwnerCnt = @OwnerCnt + 1
End
Select @Cnt = @Cnt + 1
End

--- Insert Into List of Resources (objectlock) ---
Select @Cnt = 1,@TotCnt = @DeadlockGraph.value('count(/deadlock/resource-list/objectlock)','INT')

WHILE @Cnt <= @TotCnt Begin
SELECT @child = @DeadlockGraph.query('/deadlock/resource-list/objectlock[position()=sql:variable("@Cnt")]')

Select @OwnerCnt = 1,@OwnerTotCnt = @child.value('count(/objectlock[1]/owner-list/owner)','INT')
WHILE @OwnerCnt <= @OwnerTotCnt Begin
Select @owner = @child.query('/objectlock[1]/owner-list/owner[position()=sql:variable("@OwnerCnt")]')

Select @WaiterCnt = 1,@WaiterTotCnt = @child.value('count(/objectlock[1]/waiter-list/waiter)','INT')
WHILE @WaiterCnt <= @WaiterTotCnt Begin

Select @waiter = @child.query('/objectlock[1]/waiter-list/waiter[position()=sql:variable("@WaiterCnt")]')

Insert Into #TheResource ([Entry No],OwnerPID,WaiterPID,ObjectName,IndexName)
Select @EntryNo,
Cast(@owner.query('data(/owner[1]/@id)') as NVARCHAR(MAX)),
Cast(@waiter.query('data(/waiter[1]/@id)') as NVARCHAR(MAX)),
@child.value('(/objectlock)[1]/@objectname', 'NVARCHAR(MAX)'),
' '

Select @WaiterCnt = @WaiterCnt + 1
End
Select @OwnerCnt = @OwnerCnt + 1
End
Select @Cnt = @Cnt + 1
End

--- Insert Into List of Resources (keylock) ---
Select @Cnt = 1,@TotCnt = @DeadlockGraph.value('count(/deadlock/resource-list/keylock)','INT')

WHILE @Cnt <= @TotCnt Begin
SELECT @child = @DeadlockGraph.query('/deadlock/resource-list/keylock[position()=sql:variable("@Cnt")]')

Select @OwnerCnt = 1,@OwnerTotCnt = @child.value('count(/keylock[1]/owner-list/owner)','INT')
WHILE @OwnerCnt <= @OwnerTotCnt Begin
Select @owner = @child.query('/keylock[1]/owner-list/owner[position()=sql:variable("@OwnerCnt")]')

Select @WaiterCnt = 1,@WaiterTotCnt = @child.value('count(/keylock[1]/waiter-list/waiter)','INT')
WHILE @WaiterCnt <= @WaiterTotCnt Begin

Select @waiter = @child.query('/keylock[1]/waiter-list/waiter[position()=sql:variable("@WaiterCnt")]')

Insert Into #TheResource ([Entry No],OwnerPID,WaiterPID,ObjectName,IndexName)
Select @EntryNo,
Cast(@owner.query('data(/owner[1]/@id)') as NVARCHAR(MAX)),
Cast(@waiter.query('data(/waiter[1]/@id)') as NVARCHAR(MAX)),
@child.value('(/keylock)[1]/@objectname', 'NVARCHAR(MAX)'),
@child.value('(/keylock)[1]/@indexname', 'NVARCHAR(MAX)')

Select @WaiterCnt = @WaiterCnt + 1
End
Select @OwnerCnt = @OwnerCnt + 1
End
Select @Cnt = @Cnt + 1
End

FETCH NEXT FROM Get_Tables INTO @EntryNo,@DeadlockTime,@DeadlockGraph
END
Close Get_Tables
DEALLOCATE Get_Tables

Insert Into #TheAnalysis

SELECT #TheLog.[Entry No],
CAST(LEFT(CONVERT(varchar,#TheLog.[DeadlockTime],120),19) AS datetime) as [TimeStamp],
Case When Charindex('.',Res.[ObjectName]) = 0 Then
Res.[ObjectName]
Else
Substring(Res.[ObjectName],1,Charindex('.',Res.[ObjectName],1)-1) End as [DatabaseName],
Case When Charindex('.dbo.',Res.[ObjectName],1) = 0 Then
Res.[ObjectName]
Else
Substring(Res.[ObjectName],Charindex('.dbo.',Res.[ObjectName],1)+5,len(Res.[ObjectName])) End as [ObjectName],
Res.IndexName,
CASE When Charindex('.dbo.', Res2.[ObjectName], 1) = 0 Then
Res2.[ObjectName]
Else Substring(Res2.[ObjectName], Charindex('.dbo.', Res2.[ObjectName], 1) + 5, len(Res2.[ObjectName])) End as ObjectName2,
Res2.IndexName AS IndexName2,
SP.UserName AS [Successful User],
SP.SPID AS [Successful SPID],
SP.HostName AS [Successful HostName],
SP.SQL AS [Successful SQL],
DP.UserName AS [Deadlocked User],
DP.SPID AS [Deadlocked SPID],
DP.HostName AS [Deadlocked HostName],
DP.SQL AS [Deadlocked SQL]
FROM (SELECT MIN([Line No]) AS [Line No], [Entry No], WaiterPID
FROM #TheResource
GROUP BY [Entry No], WaiterPID) AS WPID LEFT OUTER JOIN
#TheProcess AS SP RIGHT OUTER JOIN
(SELECT DISTINCT [Entry No], OwnerPID, WaiterPID, ObjectName, IndexName
FROM #TheResource) AS Res2 RIGHT OUTER JOIN
#TheResource AS Res ON Res2.[Entry No] = Res.[Entry No] AND Res2.WaiterPID = Res.OwnerPID AND Res2.OwnerPID = Res.WaiterPID ON
SP.PID = Res.OwnerPID AND SP.[Entry No] = Res.[Entry No] ON WPID.[Line No] = Res.[Line No] RIGHT OUTER JOIN
#TheProcess AS DP RIGHT OUTER JOIN
#TheLog ON DP.[Entry No] = dbo.#TheLog.[Entry No] AND DP.PID = dbo.#TheLog.DeadlockPID ON WPID.[Entry No] = dbo.#TheLog.[Entry No] AND
WPID.WaiterPID = dbo.#TheLog.DeadlockPID
ORDER BY #TheLog.[Entry No]

----------------------------------------- Summerise Data -----------------------------------------

--------- Analyse The Deadlock Query ------------------------

Select Max([Entry No]) as [Last Entry No]
,Count([Entry No]) as [Deadlock Count]
,Max([DeadlockTime]) as [Last Occurred]
,[Deadlocked SQL],[Successful SQL]
--,[Deadlocked User],[Successful User]
--,[ObjectName],IndexName
From #TheAnalysis
Group By
[Deadlocked SQL],[Successful SQL]
--,[Deadlocked User],[Successful User]
--,[ObjectName],IndexName
Order By [Deadlock Count] Desc

--------- List All Deadlocks & Summerise the count by date with moving average ---------

Declare @DLockCount Table([Entry No] BIGINT IDENTITY,[Date] datetime,[Dead Lock Count] decimal)

Insert into @DLockCount ([Date],[Dead Lock Count])

Select LEFT(CONVERT(varchar,[DeadlockTime],120),10) as [Date]
,Count(*) as [Deadlock Count]
From #TheAnalysis
Group By LEFT(CONVERT(varchar,[DeadlockTime],120),10)
Order By [Date]

SELECT
LEFT(CONVERT(varchar, Date, 120), 10) AS Date,
[Dead Lock Count],
Case When [Entry No] < 7 Then
( SELECT Cast(Round(Avg([Dead Lock Count]),2) as numeric(20,2))
FROM @DLockCount AS Ave
Where Ave.[Date] <= Main.[Date] and Ave.[Date] > DATEADD(dd,-7,Main.[Date]))
Else
( SELECT Cast(Round(Sum([Dead Lock Count])/7,2) as numeric(20,2))
FROM @DLockCount AS Ave
Where Ave.[Date] <= Main.[Date] and Ave.[Date] > DATEADD(dd,-7,Main.[Date])) End AS [Dead Lock Ave (1W)]
FROM @DLockCount AS Main

--------- List All Deadlocks & Summerise the count by table, Deadlocked User, Successful User, date ---------

Select --LEFT(CONVERT(varchar,[DeadlockTime],120),10) as [Date],
--[ObjectName],
--[Successful User],
[Deadlocked User],
--IndexName,
Count(*) as [Deadlock Count]
From #TheAnalysis
Group By --LEFT(CONVERT(varchar,[DeadlockTime],120),10)
--[ObjectName]
--[Successful User]
[Deadlocked User]
--IndexName
Order By [Deadlock Count] Desc
--Order By [Date]

------------------------------ Generate Deadlock Detail Log ------------------------------

Select [Entry No],[DeadlockTime],
CAST(LEFT(CONVERT(varchar,[DeadlockTime],120),10) AS datetime) as [Date],
[Successful User],[Successful SPID],[Successful Host],[Successful SQL],
Case When (len(ObjectName) - len(REPLACE(ObjectName,'$','')) in (1,3)) or
(PATINDEX('%[0-9]$[0-9]',ObjectName) <> 0) Then
Substring(ObjectName,PATINDEX('%$%',ObjectName)+1,len(ObjectName))
Else
ObjectName End as TableName,
[Deadlocked User],[Deadlocked SPID],[Deadlocked Host],[Deadlocked SQL],
Case When (len(ObjectName2) - len(REPLACE(ObjectName2,'$','')) in (1,3)) or
(PATINDEX('%[0-9]$[0-9]',ObjectName2) <> 0) Then
Substring(ObjectName2,PATINDEX('%$%',ObjectName2)+1,len(ObjectName2))
Else
ObjectName2 End as TableName2
From #TheAnalysis Order by [Entry No]
rbartram-847800
rbartram-847800
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 107
I use a Free product called SQL Heartbeat by SQL solutions. It lets you see visually the deadlocks as they happen and with graphic links of who is causing the deadlock. It's saved me many times when running large reports on high transaction servers in production. It's also great for after vendor updates of new software to show where issues are with their new db and stored proc code. Instead of custom doing all your own code, which is nightmare if you ever quit and another db takes your place, I prefer to purchase an inexpensive solution from a vendor so you have support, graphs and charting, alerts, etc. something like Redgate or SQL Solutions Deadlock detector.
seven.srikanth
seven.srikanth
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 463
Script not working for me... Smile

Version 2005 RTM
rama.mathanmohan
rama.mathanmohan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 164
As stated it only works on SQL2008 and above for SQL2005 you need to use WMI Query method see http://msdn.microsoft.com/en-us/library/ms186385.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search