0

I am using Microsoft SQL Server 2016

I am trying to set up email alerts for errors and I am getting this error:

07/24/2017 19:42:23,,Error,3111,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-07-24T19:42:23). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).<nl/>),22216,3029,,7/24/2017 7:42:23 PM,sa 07/24/2017 19:41:23,,Error,3110,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-07-24T19:41:23). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).<nl/>),22216,3029,,7/24/2017 7:41:23 PM,sa

However I do have recipients set not sure what I am missing here.

I ran the following query to see logs:

select log_id, event_type, log_date, description, process_id, mailitem_id,
account_id, last_mod_date, last_mod_user
from sysmail_event_log
where event_type = 'error'
order by log_date asc

select * from sysmail_faileditems

however for the 2nd query (failed items) it does have a valid email address in the recipient column. I cannot figure out what I missed in the database mail configuration.

  • This is new question from what you had before edit. You didn't gave a valid email id on E-mail address. Did you create an operator with valid email address? – SqlWorldWide Sep 18 '17 at 12:55
  • yes the email is valid – NULL.Dude Sep 18 '17 at 12:58
  • Can you try to send email using tsql to the same operator? – SqlWorldWide Sep 18 '17 at 18:09
  • I did and I got no response either, this may be my email server blocking the SMTP... I need to confirm with network admins. – NULL.Dude Sep 18 '17 at 18:10
  • mail log should give you more details. – SqlWorldWide Sep 18 '17 at 18:11
1

Your question is very broad. I hope you have some understanding or clarity on how SQL Server alerts/warnings work and what are the things you need to monitor based on your business need.

As a start read this about 3 types of alert that you can configure via SQL Server Agent.

Alerts

There are numerous articles written online about this topic and some scripts are available that you can use once you understand what you need.

If you have replication set up you will see a bunch of predefined alert show up in your SQL Server agent under alerts. Read details below:

Configure Predefined Replication Alerts (SQL Server Management Studio)

There will always be situation that you need to be alerted that is not covered above and you have to roll out your own scripts. That can be using tsql, Powershell and run via SQL Agent Jobs. If you manage many SQL Server instances you might have consider having a central monitoring/DBA instance and manage alerts from there.

Few resource I am listing for you to start with:

  • 1
    @Geo.Dude you totally rephrased your question (to a different problem) after I answered it. – SqlWorldWide Sep 18 '17 at 12:50
  • I updated the OP – NULL.Dude Sep 19 '17 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.