Post reply

How to get notification of long lock information?

  • June 8, 2006 at 9:15 am

    #61265

    Hi,

    Thanks in advance for your help.

    I am working on a blocking issue of a production db. I try to implement something so SQL Server can send me an alert if there is an application locks a resource for more than 1 hour. I use sp_add_alert procedure to create an alert and this alert suppose to send an alert to me if SQLServer:Locks Lock Wait Time > 3600 seconds. But it is not working, please advise.

    IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Block'))

    ---- Delete the alert with the same name.

    EXECUTE msdb.dbo.sp_delete_alert @name = N'Block'

    BEGIN

    EXECUTE msdb.dbo.sp_add_alert @name = N'Block', @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 60, @performance_condition = N'SQLServer:Locks|Lock Wait Time (ms)|Key|>|3600000', @include_event_description_in = 1, @category_name = N'[Uncategorized]'

    EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Block', @operator_name = N'MyDBA', @notification_method = 1

    END

  • June 8, 2006 at 9:27 am

    #642565

    I see one thing that may not be right; check this out from BOL:

    @message_id = ] message_id

    Is the message error number that defines the alert. (It usually corresponds to an error number in the sysmessages table.) message_id is int, with a default of 0. If severity is used to define the alert, message_id must be 0 or NULL.

    Note  Only sysmessages errors written to the Microsoft® Windows NT® application log can cause an alert to be sent.

    [ @severity = ] severity

    Is the severity level (from 1 through 25) that defines the alert. Any Microsoft SQL Server™ message stored in the sysmessages table sent to the Microsoft Windows NT application log with the indicated severity causes the alert to be sent. severity is int, with a default of 0. If message_id is used to define the alert, severity must be 0.

    You have both messageid and severity set to 0; from what I'm reading you need to be specific with one of them.  Also make sure your error is writing to the application log in windows.  Additionally, test to make sure the operator is receiving mail successfully by doing an xp_sendmail from QA.  Rule out that it might be a mail issue.

    HTH,

    Mark

  • June 8, 2006 at 9:37 am

    #642574

    Mark,

    Thanks for your reply. I think the sp_add_alert statement itself it fine because I just used sp_user_counters1 to set the user settable counter 1 to a number 103 and then I change the @performance_condition to N'SQLServer:User Settable|Query|User counter 1|>|100' and it works.

    So my question is whether the following condition is the correct one or not

    N'SQLServer:Locks|Lock Wait Time (ms)|Key|>|3600000'

    In my situation, I want to catch if a program lock a resource for more than 1 hour (3600000 milliseconds)

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply