Performance condition alert for blocking

Question

Performance condition alert for blocking

webrunner

SSC-Dedicated

Points: 31866
More actions
September 21, 2009 at 2:45 pm

#226460

Hello,
Is there a way to set up a SQL Server 2005 performance condition alert to monitor blocking?
I tried the following but it doesn't send an email/page when I induce blocking on the server. Even after I waited for the blocking query to wait more than 2 minutes.
Alert settings:
General
-------------
Name: TestBlockingAlert
Type: SQL Server performance condition alert
Object: MSSQL$[instance]:Locks
Counter: Lock Wait Time (ms)
Instance: _Total
Alert if counter: rises above
Value: 30000 (30 seconds)
Response
-------------
Checked Notify operators
Checked E-mail, Pager for my operator profile
Options
-------------
Include alert error text in:
E-mail
Pager
Can anyone let me know what I'm doing wrong? Should the above setup work, that is, should it send out an email and a page when the blocking wait time passes 30 seconds? Or is something set up incorrectly? Or is this not even possible with the alerts?
These are the queries I used, by the way. I opened them in two separate query windows and used AdventureWorks for testing purposes.
Query 1 (run first):
USE AdventureWorks
GO
BEGIN TRAN
UPDATE Person.Address
SET City = 'test'
-- Rollback commented out, to use later to
-- undo the changes in the transaction
-- ROLLBACK
Query 2 (run second but before running any rollback statement):
-- Try to select data from the locked table.
USE AdventureWorks
GO
SELECT *
FROM Person.Address
Query 3 (run ROLLBACK in query window 1 to undo changes and stop blocking).
Thanks for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 19, 2023 at 2:51 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

465789psw Hall of Fame Points: 3314 More actions · Answer 1

did you check in the error log in order to make sure an alert was written to the the log? looks like your setting are right.

I am assuming you have database mail set up and tested?

465789psw Hall of Fame Points: 3314 More actions · Answer 2

also check you history on your select page this will give you the number of occurrences that have actually happen

webrunner SSC-Dedicated Points: 31866 More actions · Answer 3

465789psw (9/21/2009)
did you check in the error log in order to make sure an alert was written to the the log? looks like your setting are right.
I am assuming you have database mail set up and tested?

Thanks. I'll check the error log. Yes, I have database mail set up.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31866 More actions · Answer 4

465789psw (9/21/2009)
also check you history on your select page this will give you the number of occurrences that have actually happen

Thanks - that's interesting. When I check the history for the alert, it says no occurrences.

Something must be keeping the event from happening. Does anyone have any suggestions?

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html