One user reported a deadlock error in one SQL Server box (he may found it in some application logs). Anyway, I need to find out details for this deadlock but I never enabled any trace flag like 1204 and 1222. Does SQL Server log deadlock by default? Can I find the details about that deadlock somewhere or do I have to enable trace flag and then wait for deadlock happening again?
Asked
Active
Viewed 7,027 times
4
-
2It's SQL Server2008 R2. – user3093893 Apr 08 '14 at 02:57
-
1Possible duplicate: http://stackoverflow.com/questions/12422986/sql-query-to-get-the-deadlocks-in-sql-server-2008 – Angel_Boy Apr 08 '14 at 03:09
-
1Yes, using "default extended events trace": – user3093893 Apr 08 '14 at 03:34
-
1http://dba.stackexchange.com/questions/10644/deadlock-error-isnt-returning-the-deadlock-sql/10646#10646 – user3093893 Apr 08 '14 at 04:31
1 Answers
3
I use this sql to get all the deadlocks.
DECLARE @xml XML
SELECT @xml = target_data
FROM sys.dm_xe_session_targets
JOIN sys.dm_xe_sessions
ON event_session_address = address
WHERE name = 'system_health' AND target_name = 'ring_buffer'
SELECT CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML)
FROM (SELECT @xml AS TargetData) AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
user3093893
- 119
- 1
- 9
-
1It's recoded by SQL Server by defaul and this sql can be executed in any databases since it's server level. – user3093893 Dec 09 '14 at 07:41