In SQL Server 2012, Deadlock graph is captured in system_health by default.
Under Management, right click on package0 under system_health, View target Data
Filtered by name contains xml
In SQL Server 2008 R2
Deadlock events can be queried from dynamic management views. I use the following query to get deadlock graph.
;with SystemHealth
As(
Select cast(target_data as xml)as sessionxml
from sys.dm_xe_session_targets st
inner join sys.dm_xe_sessions s on s.address = st.event_session_address
where name ='system_health'
)
Select Deadlock.value('@timestamp','datetime')as DeadlockDateTime
,cast(Deadlock.value('(data/value)[1]','varchar(max)') as xml)as DeadlockGraph
Into uat.dbo.dba3
from systemhealth
Cross apply sessionxml.nodes('//RingBufferTarget/event')as T(deadlock)
where deadlock.value('@name','nvarchar(128)') ='xml_deadlock_Report'
Enjoy my script.
Thank you for visiting my blog. My site is intended to share information and knowledge with others as well as a reference site for me.
Subscribe to:
Post Comments (Atom)
How to add a Database to AlwaysOn Availability Group with four different options
To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...
-
When you use JDBC driver to set up an application connection string to MS SQL Server database, it passed the sendStringParametersAsUnicode p...
-
Recently, we have encountered Secondary replica was in Resolving State and took 3 minutes to take over primary role when we forced reboot pr...
-
NIC Provider Order Hello 2017! I hope everyone had a great 2016. My son was born in December 2016 and 2016 was the best year for me. No...
No comments:
Post a Comment