Event alerts don't notify but performance condition alerts do

Question

Event alerts don't notify but performance condition alerts do

ccarruthers

SSC Enthusiast

Points: 112
More actions
April 14, 2017 at 11:31 am

#324364

I would like to get SQL Server 2014 event alerts via e-mail.I have set up database mail and operators, along with several severity alertsand a few alerts for specific error numbers. To test the alerts I have run a query in SSMS that is missing the '*/'on a comment (error 113, severity level 15). I've also tried a SELECT statementon a non-existent table (error 208, severity level 16). Interestingly, I do get mail when I set up aperformance condition alert (I used tempdb > 0 KB). This tells me that the database mail is setup correctly. I have verified that thealert is enabled, and that the Notify Operators box and the operator e-mail boxare both checked. Any thoughts?

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

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

unfortunately a limitation of SQL Agent alerts is that it will only send a notification if it is one of the "logged" errors:

SELECT * FROM sys.messages m WHERE m.language_id = 1033 AND severity < 19 AND is_event_logged = 1

113 and 208 are not logged. Some examples that are logged:

-- Error 823: Read Write Request Failure EXEC sp_add_alert @name = 'Error 823: Read or Write request failure', @message_id=823, @Severity=0, @enabled=1,@delay_between_responses=900, @include_event_description_in=1 EXEC sp_add_notification @alert_name = 'Error 823: Read or Write request failure', @operator_name='DBAs', @notification_method = 1

-- Error 824: Read Write Request Failure
EXEC sp_add_alert @name = 'Error 824: Logical Consistency I/O Error', @message_id=824, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 824: Logical Consistency I/O Error', @operator_name='DBAs', @notification_method = 1

-- Error 825: Read-Retry Required
EXEC sp_add_alert @name = 'Error 825: Read-Retry Required', @message_id=825, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 825: Read-Retry Required', @operator_name='DBAs', @notification_method = 1

-- Error 1101: disk space
EXEC sp_add_alert @name = 'Error 1101: Insufficient Disk Space', @message_id=1101, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 1101: Insufficient Disk Space', @operator_name='DBAs', @notification_method = 1

--Error 1105: filegroup full
EXEC sp_add_alert @name = 'Error 1105: Filegroup Full', @message_id=1105, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 1105: Filegroup Full', @operator_name='DBAs', @notification_method = 1

--Error 9002: tran log full
EXEC sp_add_alert @name = 'Error 9002: Transaction Log Full', @message_id=9002, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 9002: Transaction Log Full', @operator_name='DBAs', @notification_method = 1

--Error 6532: .Net out of Memory
EXEC sp_add_alert @name = 'Error 6532: .Net out of Memory', @message_id=6532, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 6532: .Net out of Memory', @operator_name='DBAs', @notification_method = 1

--Error 17053: OS error
EXEC sp_add_alert @name = 'Error 17053: Operating System Error', @message_id=17053, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 17053: Operating System Error', @operator_name='DBAs', @notification_method = 1

--Error 18452: untrusted
EXEC sp_add_alert @name = 'Error 18452: Untrusted Domain', @message_id=18452, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18452: Untrusted Domain', @operator_name='DBAs', @notification_method = 1

--Error 18456: general failure
EXEC sp_add_alert @name = 'Error 18456: Login Failed', @message_id=18456, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18456: Login Failed', @operator_name='DBAs', @notification_method = 1

--Error 18470: disabled
EXEC sp_add_alert @name = 'Error 18470: Account Disabled', @message_id=18470, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18470: Account Disabled', @operator_name='DBAs', @notification_method = 1

--Error 18486: locked out
EXEC sp_add_alert @name = 'Error 18486: Locked Out', @message_id=18486, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18486: Locked Out', @operator_name='DBAs', @notification_method = 1

--Error 18487: expired
EXEC sp_add_alert @name = 'Error 18487: Password Expired', @message_id=18487, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18487: Password Expired', @operator_name='DBAs', @notification_method = 1

--Error 18488: must change
EXEC sp_add_alert @name = 'Error 18488: Password Needs Changed', @message_id=18488, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18488: Password Needs Changed', @operator_name='DBAs', @notification_method = 1

Sue_H SSC Guru Points: 90860 More actions · Answer 2

You can change what messages log alerts though to get the notifications. To change 208:

sp_altermessage @message_id = 208 ,@parameter = 'WITH_LOG' ,@parameter_value = 'TRUE'

I think the only way to know for sure is to query sys.messages:
select * from sys.messages where is_event_logged = 1

I think the docs say any messages 19 or higher (and any with a forced with log) are logged but there are some 19 or higher where is_event_logged = 0. Not sure what they really do, never tested that one and I have to go eat.

Sue

ccarruthers SSC Enthusiast Points: 112 More actions · Answer 3

Thanks to Chris Harshman for the 'why' and to Sue H for the 'how'! I was able to change message 208 to a logged message and could then verify that my alerts are functioning.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

ccarruthers - Friday, April 14, 2017 2:17 PM
Thanks to Chris Harshman for the 'why' and to Sue H for the 'how'! I was able to change message 208 to a logged message and could then verify that my alerts are functioning.

Glad you got it working.
I had found out about this one by accident as I worked at a place where they had changed a lot of the events to logged events. It generated a ridiculous number of emails. I was mostly working there to help with the monitoring as important issues were being missed - the DBAs had so many emails they were deleting most and failing to see crucial ones. Notifications are great and too many notifications can easily lead to "missed" issues, problems. Just something to keep in mind.
Some things can be logged somewhere and a summary of those things can be sent out daily or however often. Things along those lines can often help in prioritizing and responding to things that really need our attention.

Sue

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Sue_H - Friday, April 14, 2017 3:18 PM
Notifications are great and too many notifications can easily lead to "missed" issues, problems. Just something to keep in mind.
Some things can be logged somewhere and a summary of those things can be sent out daily or however often. Things along those lines can often help in prioritizing and responding to things that really need our attention.

I second that advice. Too many emails just creates noise and it is only natural to ignore the noise. Log it somewhere - but that doesn't mean you have to alert on it. Figure out which is most critical and then work your way down to the less critical and then just log the non-critical while you alert on just the critical.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ccarruthers SSC Enthusiast Points: 112 More actions · Answer 6

Yep. Don't want to create alert fatigue. We only want to know about errors with severity level 17 or greater.