asked
Deadlock notification
Hi, Can we change the deadlock duration to notify and email the operators. I am getting lot of deadlock email alerts every 2 mins as the vendor code is not that good and the deadlocks disappear in 5 to 10 secs by the time i check. I am looking for the deadlock notification email or alerts sent only if the deadlock stays there for more than 20 secs. Snaphot isolation level, code etc checked. Thanks
Deadlocks don't have a duration and would not 'disappear' if given more time.. Deadlocks are events that are caused by a situation when 2 or more processes are blocking each other in such a way that will never resolve, so the Deadlock Monitor thread looks for these occurrences, resolves by killing one of the processes, and raises the alerts. Do you mean blocking? If so - what mechanism are you using to monitor and email?
But a deadlock doesn't 'carry on for 20s' - it's an event - it either happens or it doesn't. Once a deadlock condition exists it will last forever, until the deadlock monitor intervenes. Only at this point does SQL tell you there is a deadlock. I still don't understand how you think they disappear in 5 to 10 seconds.
Hi Kev, No its not blocking, i am getting deadlocks notifications frequently and i know as you said that blocking will disappear because sql OS kills of one of the process. I am looking for a solution that i can setup alerts or email notifications if the deadlocks carry on for than 20 secs then it can be notified. At the moment if deadlock happens immediately getting notified by emails.
yes you are right the alert was setup as condition not as event, object as locks, counter as number of deadlocks/sec and alert if counter raises above value >0. so obviously its alerting once the deadlock happens. so is there any way i can be notified if there is a prolonged deadlock instead immediate deadlock alerts.
No - because there is no such thing as a prolonged deadlock. If too many deadlock alerts are the real problem, then you have to fix the cause. You say this is vendor code - throw it back at them, make it their problem.
17 People are following this question.
