What to do if database mail is not working.
Mail was not being sent. First I queried
SELECT * FROM msdb.dbo.sysmail_event_log
and saw in the description field
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-03-31T12:24:10). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond [ip address]:[port number]). )”
Running
EXEC msdb.dbo.sysmail_help_status_sp ;
returned “inactive”
Some websites suggested that the people trying to send mail need to be in the msdb role DatabaseMailUserRole, so I checked that
EXEC msdb.sys.sp_helprolemember ‘DatabaseMailUserRole’ ;
but then I remembered I am a sysadmin and am not able to send mail.
Then I practically ran this
select name , is_broker_enabled from sys.databases
and this and then could send mail:
alter database msdb set single_user with rollback immediate
alter database msdb set enable_broker
alter database msdb set MULTI_USER
These procedures may be helpful too:
exec msdb..sysmail_help_principalprofile_sp
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = ‘mail’ ;
exec msdb..sysmail_stop_sp
exec msdb..sysmail_start_sp
Except the jobs still could not send mail. For some reason sql server agent did not have database mail set up. After pointing and clicking to get that together I thought I was done, except now I got this error:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-07-06T14:10:00). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.). )
This error was because the operators were defined with mapi names and not full email addresses.
Whew done!