SQL Server Agent Alerts when using performance thresholds

Question

I have set up thresholds for time behind and I'm getting an event 32040 in the eventlog when the threshold has been exceeded. I have also set up a SQL Agent Alert with a email operator. However the alert is never generated (and no email sent). Shouldn't it be possible to alert an operator as described in http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx also???

The email profile is working and also setup on the SQL Agent, and the SQL Agent has been restarted (just in case that's necessary).

 

What did I miss? Any ideas?

 

/Thomas

Answer 1

What is the SQL Agent Startup Account LocalSystem or Domain Account with Local  Admin Priv.

Answer 2

The SQL Server Agent runs as a domain user with local admin rigths. Actually I realized that sometimes I get the alert, but for some reason not always. It seems like the alert system isn't very realible, so probably this really isn't a mirroring issue but an SQL Server Agent issue.

I'm running SQL Server 2005 x64 SP2 so the error described in KB 926612 should have been fixed.

 

Answer 3

In the meantime I have realised that I never have received alerts for event 32040. However if I set up an alert for Severity 10 with the message text criteria 'time behind' I receive the alerts. So it seems like the alerts based on a specific error number doesn't work. Anybody with experience in this matter?

 

Regards

Thomas

 

Answer 4

I am also having the same issue.

 

I have setup "Unsent Log Threshold" thresholds for a database and setup a SQL Agent Alert for the event 32040 for that database. I would expect it to fire when exceeded - but it dosn't.

 

What I have noticed is happening is when the set threshold is exceeded the 32040 event is in fact raised into the SQL and Windows event logs BUT the database name does not get listed along with that rasied event. Not sure why (Bug?).

 

So when the SQL Agent Alert is looking for the Event 32040 it finds it BUT it will not fire because the alert has been defined with the database name.

 

So to make these alerts fire you need to redefine your SQL Agent Alert so that it is not database specific (ie. ALL databases) - and this works.

 

This is a bit of a pain though as you've lost granularity on threshold alerts - not good if your mirroring a number of databases.

 

I'll try a few other options - but if anyone in this forum has found a workaround for it please advise!

 

Thanks

 

Rolf

 

Answer 5

Hi Rolf

 

You are perfectly right. After changing the alert to "All databases", it actually works. In my case I only have one database which is mirrored so this workaround is sufficient for me at the moment.

 

Thanks

Thomas

 

Answer 6

Hi Rolf

Did you ever confirm that this (database name does not get listed with the 32040, 32042, 32043 and 32044 errors) is a bug?

David