September 18, 2011 at 9:59 pm
Comments posted to this topic are about the item Catching Deadlock Information in SQL Logs
Alejandro Pelc
September 19, 2011 at 4:30 am
Thanks Alejandro. Good one.
M&M
September 19, 2011 at 4:55 am
by mistake i vote 4 stars but i meant it to be 5 stars.my mistake.
Thanks a lot
September 19, 2011 at 5:24 am
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 = (SelectMax(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)
SelectXEventData.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)
SelectXEventData.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 ------------------------
SelectMax([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
WhereAve.[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
WhereAve.[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]
September 19, 2011 at 9:44 am
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.
September 20, 2011 at 5:56 am
Script not working for me... 🙂
Version 2005 RTM
September 20, 2011 at 6:59 am
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
October 15, 2019 at 2:51 am
I've tried your SQL code to display deadlock list but not working, and I'm modifiying a litlet bit an working for me. below the update script :
declare @RawLogs table (id int IDENTITY (1, 1), logdate datetime, processinfo nvarchar(50), logtext nvarchar(max))
insert into @RawLogs
exec sp_readerrorlog
declare @results table (id int IDENTITY (1,1), logdate datetime, processinfo nvarchar(50), logtext nvarchar(max))
declare @ids table (id int, processinfo nvarchar(50))
insert into @ids
select id, processinfo
from @RawLogs
where logteXt = 'deadlock-list'
order by id
declare @Startid int, @endid int, @processinfo nvarchar(50)
select top 1 @Startid = id from @ids order by id
while(@@rowcount<>0)
begin
select @processinfo = processinfo from @ids where id = @Startid
select top 1 @endid = id from @RawLogs where id > @Startid and processinfo = @processinfo order by id
insert into @results (logdate, processinfo, logtext)
select logdate, processinfo, logtext
from @RawLogs
where
id >=@Startid and
processinfo = @processinfo and
id < @endid
order by id
delete @RawLogs where id = @Startid
select top 1 @Startid = id from @RawLogs order by id
end
select logdate, processinfo, logtext
from @results
order by id
inside loop, i changed @ids become @RawLogs, because we need to update based on @RawLog not @Ids
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply