13

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I am getting this error randomly when one of my websites gets busy. I know roughly which sets of tables it is happening on but in my experience with other programs I normally get the SQL returned where the deadlock is happening. Is there a flag I should turn on to allow this to happen?

I will try and debug the deadlock itself as a seperate issue as this is my main question for now.

I am using SQL Server 2008 Standard Edition.

  • Are you able to restart the service? If you are able to bounce the service, you can add add trace flag 1204 to your startup parameters to log the details of the deadlock to the SQL Server log. > 1204: Returns the resources and types of locks participating in a deadlock and also the current command affected. > >Scope: global only – Tevo D Jan 13 '12 at 13:49
  • 1
    Use the configuration manager. Under SQL Server Services, right click and open properties. Go to Advanced tab, startup parameters. You will have entries for the location of the master database files and such. Add ;-T1204 for the trace flag to end and restart the service. – Tevo D Jan 13 '12 at 14:03
  • 4
    Why restart the service? DBCC TRACEON (1204, -1) – Mark Storey-Smith Jan 13 '12 at 17:21
  • From msdn.microsoft.com/en-us/library/ms188396.aspx: Behavior Changes: In SQL Server 2000, a simple DBCC TRACEON (1204) is enough to enable deadlock reporting to the error log. In SQL Server 2008, you must enable the flag globally because the session-level flag is not visible to the deadlock monitor thread. – Tevo D Jan 13 '12 at 17:24
  • 2
    @TevoD - The -1 parameter to DBCC TRACEON signifies global. – Martin Smith Jan 13 '12 at 17:28
25
+50

The data you need is recorded in the default extended events trace.

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   
             XEventData.XEvent.query('(data/value/deadlock)[1]')  AS DeadlockGraph,
             CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML) AS DeadlockGraph,
              XEventData.XEvent.value('(./@timestamp)[1]', 'DATETIME2') AS [DateTime]
FROM   (SELECT @xml AS TargetData) AS Data
       CROSS APPLY 
       TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent) 
ORDER BY [DateTime] DESC

Though it won't be there any more if you have restarted the service -e.g. to apply a trace flag or if the buffer has cycled in the meantime.

You can set up your own extended events trace that stores the deadlock graph to a file target for persistent non volatile storage. Example Code here. I personally find the deadlock graph XML more friendly than the trace flag output.

Edit

  1. @MartinC points out in the comments that on instances of SQL Server that don't have all the updates there might be a problem with it generating invalid XML. The fix for that is to do some search and replace and use 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 in the SELECT list as described here.
  2. Wayne Sheffield has posted a useful script to shred the deadlock graph XML into tabular format here.
2

The accepted answer did not work for me consistently. The ring buffer apparently is known to drop events in certain circumstances..

ConnectItem

Ring Buffer Issues

The system_health log event files can parsed (from this answer):

with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
  from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where substring(event_data, 1, 50) like '%"xml_deadlock_report"%'  
) 
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc

The XdlFile field can be saved to an .xdl file and read into SSMS. Tested in Sql Server 2012.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.