Repeated SQL Alerts

Question

Repeated SQL Alerts

paulhumphris

Hall of Fame

Points: 3496
More actions
May 14, 2003 at 5:41 am

#64673

I have created a SQL Alert which raises and email notification if the number of Deadlocks/sec raises above 1.
This Alert has been sitting dormant on the server for some time, but this morning I received an alert due to a dealock which had occured. Fine, I thought....I checked the SQL Error log (I have trace flags 1204 & 3605 in place)...and yes a single deadlock had occured.
However, I am still receiving email notifications from the Alert.....every 15-20 seconds it detects a further deadlock, although no deadlock info is being written to the errorlog, there are no blocked tasks in sp_who/sp_who2 and I have even set up a performance monitor on the same Deadlock/sec stat and the graph is flat zero all the time.
Has anyone go any clues as to why this alert is still firing and allegedly finding deadlocks (which no other part of the systems in finding)?

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

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

The reason why you still get this alert is the way SQL Server detects these situations. What the alert is doing is it looks in the sysperfinfo table. If you look into that table you will see that the value for deadlocks/sec = 1. Once anther deadlock occurs the value will be increased to 2. So as BOL says: In most cases, this will be a level or monotonically increasing counter that counts occurrences of the instance event. Not the actual current events.

I once ran into the same problem with replication conflicts/sec and was able to find a workaround. In your case you might build some alert which checks if the value in sysperfinfo has increased since the last check.

Hope this helps

M

[font="Verdana"]Markus Bohse[/font]

paulhumphris Hall of Fame Points: 3496 More actions · Answer 2

Thanks MarkusB for the prompt response.

I have now written a script to run as a SQL Job when the Alert fires, this script drops and recreates the Alert with the threshold value incremented the value in sysperfinfo.

Steve Velasco Old Hand Points: 332 More actions · Answer 3

Hi Paul .. can you send me a sample of your written script to run as a SQL Job when the alert fires? THanks.

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Or post it here if you think it would be useful to others !

Luke Nguyen SSC Journeyman Points: 97 More actions · Answer 5

Hi Mark,

How did you determine whether the value in sysperfinfo has increased since the last check?

Thank!

LukeN