|
We've got some process which occasionally causes deadlocks to occur in our SQL Server 2005 database. I've been trying to use Jonathan Kehayias' methoded to detect deadlocks in SQL Server (http://tinyurl.com/yzlfgyc), but it hasn't worked. After several failed attempts to get a good deadlock, this morning we finally had one, and I had the SQL Profiler job running, as Jonathan specified. However, nothing was captured, so I still don't really know what's causing it. I've really tried following Jonathan's recommendations, so I'm wondering what I might have done wrong? Is there something kind of tricky I may have left out? When a lock occurs, does it show up in the track, or does it only show up after you stop the trace?
(comments are locked)
|
|
Rather than using Profiler, just enable the appropriate trace flags for recording deadlock events to the SQL Server Error Log. See Detecting and Ending Deadlocks for more details and in particular the section titled "Deadlock Information Tools" For those that may be interested, take a look at the following blog post Blocking is not Deadlocking, to understand the differences between Blocking and Deadlocking.
(comments are locked)
|
|
Unless you were getting hard 1205 exceptions raised by to the application from SQL Server that have an error message similar to the one below you weren't having deadlock problems.
Deadlocks result in a 1205 exception and hard failure of the connection since it is killed by the Deadlock Monitor to free the locks being held. The following article also covers how to capture deadlocks with Profiler, as well as includes the code to create a deadlock for testing the methodology. http://www.sqlservercentral.com/articles/deadlock/65614/ If you can get the deadlock graph and post it back we can better assist in offering ideas for how to resolve the deadlock. The deadlock example on the above article is one of the most common deadlocks that people encounter.
(comments are locked)
|
|
In SQL 2005, 2008 you can configure the blocked process threshold in seconds and then capture the Blocked Process Report Event in profiler which you can use to help track down blocking. For more information look at http://msdn.microsoft.com/en-us/library/ms191168.aspx The lower the threshold is, the more overhead it will add to your server, so use with cation.
(comments are locked)
|
It should show up in the trace during execution.
To be clear, are you certain that you are experiencing Deadlock events and not Blocking?
No, I am not. I'm not sure how to determine the difference between the 2. (And, BTW, I choose "Blocking" for the tag for this post, because "Deadlocks" was not available to me to choose.)
I've added a deadlock tag to it. You need to get over a certain level of reputation in order to be allowed to create new tags. See the FAQs.
Ah, OK, thank you, Melvyn, for explaining the means by which new tags get added.