Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 13 '12 at 16:59

This question came from our site for professional and enthusiast programmers.

    
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
add comment

1 Answer 1

up vote 13 down vote accepted

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 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) 

Though it won't be there any more if you have restarted the service 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.
share|improve this answer
    
Trace flag 1222 will enable the XML version to be logged. Which is handy as it isn't transient, it persists in the SQL Server log. –  Tevo D Jan 13 '12 at 14:48
    
@TevoD - It isn't actually XML though is it? Doesn't have any tags. I prefer looking at the actual XML so you can easily collapse and expand sections of interest. Plus there are scripts around to shred it and make diagnosis easy. –  Martin Smith Jan 13 '12 at 14:51
    
1204 is text, 1222 should be XML. –  Tevo D Jan 13 '12 at 15:26
    
@TevoD - No it isn't. An example of the output is here –  Martin Smith Jan 13 '12 at 15:29
1  
With multiple victims earlier versions of SQL Server 2008 generate invalid xml in the system_health session, see this post for query to handle this issue sqlservercentral.com/articles/deadlock/65658 –  MartinC Jan 13 '12 at 21:20
show 10 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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