none
Unable to get notification when deadlock occurs RRS feed

  • Question

  • Hi,

      I am facing this problem with SQL Server 2000 as well as with SQL Server 2005.  I want to get notified when a deadlock occurs. This is what I have done.

    I have setup an SQL Server Agent alert for the error number 1205. The alert is enabled and in response to the alert I am invoking a job and also tried email and net send etc.

    Now I simulated deadlock situation by using two connections and received the 1205 error. However when I go back to the alert and see it does not invoke the job nor send message nor email. I find the number of occurrances of the alert as 0 which means that the alert never occurred. Why am I not able to trap the 1205 error message and get a notification?

    I understand that I can enable trace flag 1204 and trace flag 3605 to get the deadlock event and the complete description logged to the sql server error log. But my question is why am I not able to use the 1205 event in the sql server agent alert. Even if I enable 1204 and 3605 I am only able to get the information of the deadlock in the error log but the alert is not getting triggered.

    I am able to get all other alerts except the alert for 1205 and in general for any severity 13 alerts they are not getting triggered.

    Any help is appreciated. I got this problem with sql server 2000 sp4 and sql server 2005 sp1.

    Regards,

    Ravi

     

     

     

    Monday, June 26, 2006 4:01 PM

All replies

  • Hi,

      Can someone please respond to this. Am I missing something here? I dont think this is an issue as the same behavior is obtained with both sql server 2000 and 2005.

    Regards,

    Ravi

    Tuesday, June 27, 2006 3:49 PM
  • Hi, (sorry if this is a bit too late)

     

    In 2000 what you need to do is use sp_altermessage and enable logging for the 1205 message id.

     

    This will then log a one liner in the error log regarding the deadlock.

     

    However, the functionality of sp_altermessage in 2005 has been changed, so you are no longer able to enable logging for message IDs less than 50,000 (i.e. system messages).

     

    I raised this with Microsoft Support, and there is no fix for this. We'll see if there's anyway it could be included in 2008, as this is a big piece of functionality that a lot of my clients rely on in 2000.

    Monday, August 20, 2007 1:54 AM