April 14, 2011 at 1:05 pm
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...
April 14, 2011 at 1:17 pm
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
April 14, 2011 at 1:54 pm
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...
April 15, 2011 at 2:09 am
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
April 15, 2011 at 6:59 am
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...
April 15, 2011 at 7:26 am
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
April 15, 2011 at 7:34 am
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...
April 15, 2011 at 8:07 am
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
April 15, 2011 at 8:15 am
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...
September 29, 2011 at 6:35 am
I know this is an old post, but has this been resolved. I'm having the same problem!
September 29, 2011 at 9:21 am
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...
September 30, 2011 at 12:06 pm
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.
October 27, 2016 at 3:56 pm
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