August 1, 2011 at 12:30 pm
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 80) was deadlocked on lock
communication buffer resources with another process
and has been chosen as the deadlock victim.
Rerun the transaction.
what should i need to do not to get this message again when i run the query?
August 1, 2011 at 12:44 pm
i mean set deadlock priority HIGH before the query is the only solution for this problem
August 1, 2011 at 12:48 pm
A workaround, yes. The only solution, not even close.
The solution is to identify the cause of the deadlocks and fix it.
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2011 at 12:58 pm
Along with the output of traceflag 1222 (if on SQL Server 2005 or higher), you can capture a profiler trace with deadlock events and statement level events to get the deadlock graph in graphical format. Once you get the session IDs that were involved in the deadlock, you can trace back to see which statements actually were involved in the deadlock chain and why.
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: TroubleshootingSQL
Twitter: @banerjeeamit
August 2, 2011 at 1:31 pm
You may be able to retrieve the deadlock graph using Extended Events.
Please read this article:
Retrieving Deadlock Graphs with SQL Server 2008 Extended Events[/url]
Note: if you're on 2008 (not R2) then it requires that you are on at least Cumulative Update #1. If you're on R2 then you should not need to worry, but I am not certain whether RTM contains the bug or whether a patch is required.
If you are able to retrieve the graph with this method please post it to the thread. Post it by attaching it to the thread as a .txt file containing the XML graph so the site and browser do not munge the XML tags as part of the posting/rendering process.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 3, 2011 at 4:59 am
Check out this blog series for deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply