Alerts not firing in 2005

Question

Alerts not firing in 2005

Colin Morgan

Newbie

Points: 1
More actions
June 1, 2006 at 10:28 am

#114389

Cannot get alerts firing in SS 2005. Created a simple alert to test emailing an operator if error 208 (object not found) occurs in any database. But the alert will not fire (history shows that the alert has never fired).
Via other methods we have discovered that email works. No errors in the logs. No messages. Nothing.
This is the only only 2005 instance in the company - currently under test. Repeated installs and upgrades to SP1 have done nothing to solve this problem.
Any thoughts?
Thanks

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

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

Ian Struthers SSC Veteran Points: 274 More actions · Answer 1

Colin - I had a similar problem recently. I'd installed 2005, rebooted then applied SP1 and created my new alert on 50001 (sp_addmessage @msgnum=50001, @severity=10, @msgtext='My alert', @with_log=TRUE). The alert never fired (history count was laways 0) until after I'd stop/started the SQL Server & Agent (so I don't know which one it was that cured it)

HTH

Ian

charles evans SSCommitted Points: 1865 More actions · Answer 2

We're experiencing the same problem. Has anything come of this to date?

Tnx,

Charles

Balmukund SSCertifiable Points: 6648 More actions · Answer 3

If you are looking for way to fire Alert for System Messages...

Then it's a Bad news...

In SQL 2005 "System messages cannot be modified with sp_altermessage

http://msdn2.microsoft.com/en-us/library/ms175094.aspx

sp_altermessage cannot be used to change the logging behavior of system messages

Behavior Changes to Database Engine Features in SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

To audit system messages, use

SQL Trace and the User Error Message Event Class

http://msdn2.microsoft.com/en-us/library/ms190953.aspx .

For more information, see Introducing SQL Trace http://msdn2.microsoft.com/en-us/library/ms191006.aspx

charles evans SSCommitted Points: 1865 More actions · Answer 4

Thank you for posting that link; it's a very good summary of the DbE changes between 2000 and 2005. This would seem to explain the problem, but here are some things that make me think, it is not all inclusive.

1.) I received one and only one alert for a 9002 Transaction Log is full error (when there were at least 1 an hour that should have been sent).

2.)An alert designed to be fired on a custom test error with RAISERROR with LOG works.

All of the documentation I've read from BOL on setting up the SQLAgent and Database Mail were followed to a tee.

This is happening both on a x86 Standard Edition Development box and a 64 bit Developer's Edition box. I also checked into ISA to make sure smtp was not being blocked and it isn't. Nothing is in cue and the history doesn't update on the alert, so this would rule out a blocking issue anyways.

Cheers,

Charles