January 11, 2016 at 1:33 pm
I have set up job failure notification by mail, but somehow I can never receive the mail. msdb.dbo.sysmail_sentitems shows the mail was "sent" with all the mail details. I can send and receive test mail from Database Mail successfully.
I checked Clutter and Junk Email in my Inbox, nothing was found.
So what has gone wrong?
January 11, 2016 at 2:13 pm
you are sure they were REALLY sent, right?
first check and make sure they were not receiving errors from the mailserver:
SELECT top 100
mail.send_request_date As SentDate,
sent_status As Reason,
err.[description],
mail.*
FROM [msdb].[dbo].[sysmail_allitems] mail
inner join [msdb].[dbo].[sysmail_event_log] err
ON err.mailitem_id = mail.mailitem_id
WHERE mail.sent_status <> 'sent'
order by mailitem_id desc
Check to see if there's a specific error there that is stopping your outbound mails...
after that, i'd say that you need to actually check with the mailserver guys; they might have rules that send emails into a blackhole for various reasons (ie NotARealMailbox@mydomain.com), but which still returns the "200 OK" command to the sender.
in my case, i had to modify my settings to use a specific account for sending, that really existed; previously i could send fake@mydomain.com, but the network guys tightened the rules,and adversely affected my notifications as a result.
Lowell
January 11, 2016 at 2:13 pm
Did you enable the Mail Profile in the SQL Server Agent Properties?
January 11, 2016 at 2:15 pm
SQL Server Agent > Properties > Alert System >Enable mail profile
January 11, 2016 at 2:37 pm
Thanks for the input.
I checked the SQL Server Agent and the mail profile is enabled for the Alert System.
There is no error message in the mail tables either.
The SMTP credential does use a specific user account and that is how it is set. The same email credential is used for another DEV sql server, which sends out failure email without any problem.
So still don't know what is wrong.
January 11, 2016 at 2:51 pm
Michelle-138172 (1/11/2016)
Thanks for the input.I checked the SQL Server Agent and the mail profile is enabled for the Alert System.
There is no error message in the mail tables either.
The SMTP credential does use a specific user account and that is how it is set. The same email credential is used for another DEV sql server, which sends out failure email without any problem.
So still don't know what is wrong.
hmmm...i would expect an error, and not disappearing mails.
mail servers often have a white list, where specific servers, or servers in a specific IP range, can send anonymous emails.
your Dev server sounds like it's fine, but this server might not be in the whitelist. i'd expect an explicit error if it was not on the whitelist.
this last one is a rare situations; the last thing, which doesn't hurt to do, is to see if you have a broken mail queue;
i've only had to fixe this twice in my career(so far), but I've got the script:
run this query; on msdb there is a specifically named queue for the db mail.:
EXECUTE msdb.dbo.sysmail_help_status_sp;
SELECT COUNT(*) FROM msdb.dbo.ExternalMailQueue
if you get non zero results, you can try using this to clear out the queue:
EXECUTE msdb.dbo.sysmail_help_configure_sp;
EXECUTE msdb.dbo.sysmail_stop_sp;
EXECUTE msdb.dbo.sysmail_help_status_sp;
EXECUTE msdb.dbo.sysmail_start_sp;
--#################################################################################################
--reset a broken queue with hung messages
--#################################################################################################
USE msdb;
GO
IF EXISTS (SELECT COUNT(*) FROM msdb.dbo.ExternalMailQueue)
BEGIN
ALTER QUEUE ExternalMailQueue WITH STATUS = ON
DECLARE @ch UNIQUEIDENTIFIER;
DECLARE @message_type NVARCHAR(256);
DECLARE @message_body NVARCHAR(MAX);
DECLARE @ctr BIGINT;
DECLARE @ctr2 BIGINT;
WHILE (1=1)
BEGIN
RECEIVE @ch = conversation_handle, @message_type = message_type_name FROM msdb.dbo.ExternalMailQueue;
SET @ctr2 = @ctr2 + 1;
SET @ctr = (SELECT COUNT(*) FROM msdb.dbo.ExternalMailQueue);
IF @ctr = 0
BREAK;
END;
END --IF
Lowell
January 11, 2016 at 2:56 pm
Thanks a lot, Lowell. The result did come back as 0. 🙁
I'll talk to the mail guys and see if they have a white list or if there are any other things they might be doing for my server.
January 14, 2016 at 4:52 am
Have you checked with the network guys if the port for mail is open on that server? I think its port 20?
January 14, 2016 at 12:24 pm
This is embarrassing. It turned out the problem was I misspelled the email address for the Operator. Checked that spot a million times but didn't notice it was spelled wrong. I really need to make the font display bigger on that computer.
That also brings up an interesting question though. So if the Operator has an invalid address, you won't get a recipient error for the Database Mail?
January 14, 2016 at 12:43 pm
Aside from the @ sign and .com (gov,edu), What would be considered invalid.
It would probably check e-mail structure (but I don't even think it checks for that).
Since it is not Active Directory Integrated and there is not a way (as far as I know), it cannot verify the information that was set manually. When creating an Operator it accepts whatever input you specify. For example if you create an Operator named James Wilson and you type Jmes Wilson it will accept it. Also, if you create jwilson@espn.com as the email but it is misspelled *** jlson@espn.com or jlson@espn (forget the .com) it will accept it.
January 14, 2016 at 1:08 pm
Thanks for the input. I meant if the email recipient doesn't exist, wouldn't the system get a delivery failure and log the error? I guess it can't be done because only the mail server would know the failure but the sql server wouldn't.
jcarranza 23978 (1/14/2016)
Aside from the @ sign and .com (gov,edu), What would be considered invalid.It would probably check e-mail structure (but I don't even think it checks for that).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply