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 and code to get the best help
- 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 and code to get the best help
- 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 and code to get the best help
- 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 and code to get the best help
- 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 12 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply