July 11, 2013 at 4:14 am
Good Day
We try to import data into a tabe from a file , but we get deadlocks on insert . We added indexes before and we suspected they were the cause and we removed them . But this did not solve the problem. I checked for open transactions using dbcc opentran and it showed that the replication has an open transaction . We did activate all the traceflags as per SQL Server guidelines on the web . We get the table that is the victim, but we cannot identify the process that causes the problem . Any ideas ? We are a bit desperate.
Thanks in advance
July 11, 2013 at 9:42 am
Capturing a "deadlock graph" via server side trace or profiler can be used to capture the xml for the actions causing the deadlock. the xml can be saved as a .xdl so you can view a graphical representation of the deadlock or alternativly using an application like XMLNotepad 2007 you can view the deadlock information in tree view.
This allows you to see what exactly is happening at the time and what resources are being locked.
Posting up the deadlock xml here would help so we can see the problem for ourselves.
This query can be used to retrive deadlock xml data using the standard extended event as well which save the use of trace flags or traces.
SELECTevent_xml.value('(./@name)', 'varchar(1000)') as event_name,
event_xml.value('(./@timestamp)', 'datetime') as event_time,
event_xml.value('(./data[@name="xml_report"]/value)[1]', 'varchar(max)') as deadlock_graph
FROM #ring_buffer_data
CROSS APPLY xml_data.nodes('//event[@name="xml_deadlock_report"]') n (event_xml)
WHERE event_xml.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
MCITP SQL 2005, MCSA SQL 2012
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply