InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 03:38:03 GMT20http://www.sqlservercentral.com/Forums/Topic1177065-1502-1.aspxAs 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.aspxTue, 20 Sep 2011 06:59:20 GMTrama.mathanmohanhttp://www.sqlservercentral.com/Forums/Topic1177065-1502-1.aspxScript not working for me... :)Version 2005 RTMTue, 20 Sep 2011 05:56:20 GMTseven.srikanthhttp://www.sqlservercentral.com/Forums/Topic1177065-1502-1.aspxI 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.Mon, 19 Sep 2011 09:44:25 GMTrbartram-847800http://www.sqlservercentral.com/Forums/Topic1177065-1502-1.aspxA 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 ONset dateformat ymdSelect 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_DeadlockEvents2008IF OBJECT_ID('tempdb..#TheProcess') IS NOT NULL Drop Table #TheProcessIF OBJECT_ID('tempdb..#TheLog') IS NOT NULL Drop Table #TheLogIF OBJECT_ID('tempdb..#TheResource') IS NOT NULL Drop Table #TheResourceIF OBJECT_ID('tempdb..#TheAnalysis') IS NOT NULL Drop Table #TheAnalysisCreate 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 intSet @BugFlag = (Select Max(Case When charindex('deadlock-list',XEventData.XEvent.value('(data/value)[1]','varchar(max)')) &gt; 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 = 1Begin 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)'), '&lt;victim-list&gt;', '&lt;deadlock&gt;&lt;victim-list&gt;'), '&lt;process-list&gt;','&lt;/victim-list&gt;&lt;process-list&gt;')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]EndElseBegin 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 bigintDeclare @DeadlockTime datetimeDeclare @DeadlockGraph xmlDeclare @PID nvarchar(max)Declare @Cnt int, @TotCnt int,@OwnerCnt int,@OwnerTotCnt int,@WaiterCnt int,@WaiterTotCnt intDeclare @child xml,@owner xml,@waiter xmlDeclare Get_Tables CURSOR FAST_FORWARD FOR (Select [EntryNo],[AlertTime],[DeadlockGraph] From #ZZ_DeadlockEvents2008 (nolock)--Where [AlertTime] &gt; '2009-04-01')Open Get_TablesFETCH NEXT FROM Get_Tables INTO @EntryNo,@DeadlockTime,@DeadlockGraphWHILE @@FETCH_STATUS = 0BEGIN 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 &lt;= @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 &lt;= @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 &lt;= @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 &lt;= @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 &lt;= @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 &lt;= @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 &lt;= @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 &lt;= @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 &lt;= @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 &lt;= @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,@DeadlockGraphENDClose Get_TablesDEALLOCATE Get_TablesInsert Into #TheAnalysisSELECT #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.DeadlockPIDORDER 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],IndexNameFrom #TheAnalysisGroup By [Deadlocked SQL],[Successful SQL] --,[Deadlocked User],[Successful User] --,[ObjectName],IndexNameOrder 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 #TheAnalysisGroup 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] &lt; 7 Then ( SELECT Cast(Round(Avg([Dead Lock Count]),2) as numeric(20,2)) FROM @DLockCount AS Ave Where Ave.[Date] &lt;= Main.[Date] and Ave.[Date] &gt; 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] &lt;= Main.[Date] and Ave.[Date] &gt; 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 #TheAnalysisGroup By --LEFT(CONVERT(varchar,[DeadlockTime],120),10) --[ObjectName] --[Successful User] [Deadlocked User] --IndexNameOrder 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) &lt;&gt; 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) &lt;&gt; 0) Then Substring(ObjectName2,PATINDEX('%$%',ObjectName2)+1,len(ObjectName2)) Else ObjectName2 End as TableName2From #TheAnalysis Order by [Entry No]Mon, 19 Sep 2011 05:24:01 GMTrama.mathanmohanhttp://www.sqlservercentral.com/Forums/Topic1177065-1502-1.aspxby mistake i vote 4 stars but i meant it to be 5 stars.my mistake.Thanks a lotMon, 19 Sep 2011 04:55:22 GMTlital-494950http://www.sqlservercentral.com/Forums/Topic1177065-1502-1.aspxThanks Alejandro. Good one.Mon, 19 Sep 2011 04:30:30 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1177065-1502-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/deadlocks/74829/">Catching Deadlock Information in SQL Logs</A>[/B]Sun, 18 Sep 2011 21:59:19 GMTAle Pelc