SQL Alerts

  • I've created an alert to email me if Lock Wait times exceed 10 seconds. It fires the email after the locking has stopped. I've changed the lock wait time to 30 seconds, and still it waits until after the locking has stopped to email me :pinch:. Why is this and how do I fix it?

    Thanks

    There is an exception to every rule, except this one...

  • Can you elaborate on how you did configure and are using the alert ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • type: SQL Server Performance condition alert

    Object: SQLServer:Locks

    Counter: Lock Wait Time (ms)

    Instance: _Total

    Alert if counter: rises above Value: 1

    Response: Notify operators (email works)

    The alert technically works, but only after the locking has resolved.

    There is an exception to every rule, except this one...

  • I tested this script:

    USE [msdb]

    GO

    /****** Object: Alert [DBA_LockingTreshold] Script Date: 04/15/2011 08:30:59 ******/

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

    EXEC msdb.dbo.sp_delete_alert @name=N'DBA_LockingTreshold'

    GO

    EXEC msdb.dbo.sp_add_alert @name=N'DBA_LockingTreshold',

    @enabled=1,

    @delay_between_responses=10,

    @include_event_description_in=1,

    @notification_message=N'DBA_LockingTreshold alert',

    @performance_condition=N'MSSQL$GNKD002:Locks|Lock Wait Time (ms)|_Total|>|1',

    @job_id=N'00000000-0000-0000-0000-000000000000'

    GO

    EXEC msdb.dbo.sp_add_notification @alert_name=N'DBA_LockingTreshold', @operator_name=N'ALZDBA', @notification_method = 1

    GO

    The allert will respond every 10 seconds.

    I created a lock on a talbe using:

    use DDBAStatistics

    go

    begin tran

    update T_Server

    set DienstStatus = 'b'

    print 'bezig'

    /* commented because I want to keep the transaction open to test the locking scenario

    rollback tran

    print 'Rollbacked'

    */

    On a second connection I then perform "select *from T_Server" of course this one must wait for the transaction to be completed.

    I've waited some time and received a notification before I rollbacked the transaction.

    btw I'm testing with SQL2008R2DevEdtn + CU6

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I deleted mine, executed your script, same issue. Doesn't matter how long I set it up to lock, as soon as it frees up the email is sent. Even locked the resource the same way for 2 minutes. After I committed the tran, it sent the email. Do you think it's a setting at the SQL Server level?:unsure:

    There is an exception to every rule, except this one...

  • is this your first alert ?

    Did you restart sqlagent after installing the alert ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • It is the first alert on both the dev server and my local sql laptop. I have also restarted sql agent on both, and still same issue. I have no idea why its waiting to send email.

    There is an exception to every rule, except this one...

  • Can you post your @@version information ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • server:

    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    laptop:

    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

    same results on both.

    There is an exception to every rule, except this one...

  • I know this is an old post, but has this been resolved. I'm having the same problem!

  • I was using wrong Performance condition :pinch:. Try Object SQLServer:General Statistics and the Counter: Processes blocked.

    There is an exception to every rule, except this one...

  • Thanks for the quick response!

    I wonder though if this is always the best counter since it doesn't relate to the amount of time locks are being held. It would be good if there was a high number of concurrent blocks, right? However, if I had just 2 processes that were blocking each other for an extended period of time (i.e. not a deadlock that would roll 1 back), I might not find out about it.

  • Hi my! actually i have the same problem my alert was sending just when after the locking has stopped, now my alert is configured in this way but it doesn't work.

    Object: General statistics, Counter: Processes blocked, Instance: , alert in counter: Rises above Value: 1

    But i am not receiving any email, do you think the problem is in the instance?? because it's in blank??

    Thank's in advance.

    Regards!

Viewing 13 posts - 1 through 12 (of 12 total)

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