Script to get an alert\email notification when a dead lock occurs

• madhu-686862

  SSChampion

  Points: 10707

  More actions

  December 6, 2008 at 9:53 pm

  #132843

  Hi,

  could you plz provide a Script to get an alert\email notification when a dead lock occurs.Actually Iam using SQL Server 2000.Sorry for posting in SQL Server 2005 forums.I ran the below script in Query analyzer, But Iam getting an error:

  sp_altermessage @message_id = 1205,

  @parameter = 'write_to_log',

  @parameter_value = 'true'

  Error:

  Server: Msg 15176, Level 16, State 1, Procedure sp_altermessage, Line 34

  The only valid @parameter value is 'WITH_LOG'.

  So, Can we put above script in SQL Server agent as job to get an email notification when the dead lock occurs?

  Thanks for your suggestions

• hemanth.damecharla

  SSCommitted

  Points: 1740

  More actions

  December 7, 2008 at 6:14 am

  #908918

  [font="Verdana"]I don't think so. See the following extract.

  ----------------------------------------------

  http://doc.ddart.net/mssql/sql70/sp_ae-az.htm

  ----------------------------------------------

  The effect of sp_altermessage with the WITH_LOG option is that it changes the logging behavior of an existing message.

  If a message has been altered to be WITH_LOG, it is always written to the Windows NT application log, regardless of how a user invokes the error.

  Even if RAISERROR is executed without the WITH LOG option, the error is written to the Windows NT application log.

  System messages (such as 605), as well as user messages added by sp_addmessage, can be modified by using

  As for sending the mail, you need to look at this topic for more information

  http://www.sqlservercentral.com/Forums/Topic527864-110-1.aspx

  A friendly piece of advice before you ask for scripts...there are a lot available in the scripts section.

  Secondly, if you ask for a script in a forum post; it will be a hit and miss as we might see it and feel like responding

  or we might see it and just ignore because no effort was put in by 'you' other than requesting the script.

  Read a little bit, gather information and get your doubts clarified if you can't find a solution or,

  if you are having trouble with code you have written. It really helps to know that you have put in some effort before

  coming here 🙂

  Please, read this post by Jeff, Moden:

  For better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/

  [/font]

  Regards,

  -Hope is a heuristic search :smooooth: _~Hemanth