October 9, 2008 at 10:10 am
Hi,
I am trying to figure out how non-working email addresses sent from Database Mail can be either forwarded to another email address or logged. It seems the only time they make to sysmail_log is when a test email is sent. When i specify a profile, nothing shows up in the mail box I have set in the profile for Replies, nor does anything show in sysmail_mailitems that is did get sent, such as sent_status = 2.
Does anyone know how to do this? I have to know when emails being sent out aren't valid. . .
Thanks
October 9, 2008 at 11:30 am
Are you sending them directly from the SQL server or via some other SMTP server on your network? Various SMTP servers have differing abilities for logging and such, but if you're just handing messages off to a server like Exchange, Sendmail, or Postfix you should be able to get that information from there. some sort of failed message queue or something...
-Luke.
October 9, 2008 at 12:11 pm
Exactly what I am talking about, and yes I am contacting our SMTP group to discuss that, BUT NOW I HAVE ANOTHER ISSUE:
When there is a bad email, Database Mail continues to try and send the email 2 or 3 times per bad email address, so if my to: field is valid, but the two in my CC: are not, it will send out 4 emails which the valid to: will get. COMPLETELY UNACCEPTABLE.
Anyone know where I can set that off? I dont want any re-tries. I know this is probably buried in Microsoft's code in msdb. . .
I used to love DB Mail. . . :hehe:
October 9, 2008 at 12:22 pm
According to the following it would appear that Database mail only retries if it can't contact your SMTP server. It would appear that your issue with the multiple sends with an invalid recipient address is on that side of the house.
FROM Troubleshooting Database Mail: General steps
http://msdn.microsoft.com/en-us/library/ms187540(SQL.90).aspx
Determine if problems with Database Mail affect all accounts in a profile or only some accounts
1.
If you have determined that some but not all profiles can send mail, then you may have problems with the Database Mail accounts used by the problem profiles. To determine which accounts are successful in sending mail, execute the following statement:
Copy Code
SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems ;
2.
If a profile which is not working does not use any of the accounts listed, then it is possible that all the accounts available to the profile are not working properly. To test individual accounts, use the Database Mail Configuration Wizard to create a new profile with a single account, and then use the Send Test E-Mail dialog box to send mail using the new account.
3.
To view the error messages returned by Database Mail, execute the following statement:
Copy Code
SELECT * FROM msdb.dbo.sysmail_event_log ;
Note:
Database Mail considers mail to be sent, when it is successfully delivered to a SMTP mail server. Subsequent errors, such as an invalid recipient e-mail address, can still prevent mail from being delivered, but will not be contained in the Database Mail log.
Configure Database Mail to retry mail delivery
1.
If you have determined that the Database Mail is failing because the SMTP server cannot be reliably reached, you may be able to increase your successful mail delivery rate by increasing the number of times Database Mail attempts to send each message. Start the Database Mail Configuration Wizard, and select the View or change system parameters option. Alternatively, you can associate more accounts to the profile so upon failover from the primary account, Database Mail will use the failover account to send e-mails.
2.
On the Configure System Parameters page, the default values of 5 times for the Account Retry Attempts and 60 seconds for the Account Retry Delay means that message delivery will fail if the SMTP server cannot be reached in 5 minutes. Increase these parameters to lengthen the amount of time before message deliver fails.
Note:
When large numbers of messages are being sent, large default values may increase reliability, but will substantially increase the use of resources as many messages are attempted to be delivered over and over again. Address the root problem by resolving the network or SMTP server problem that prevents Database Mail from contacting the SMTP server promptly.
October 9, 2008 at 1:21 pm
HOLY CRAP LUKE THAT IS EXACTLY WHAT I NEEDED TO SEE
The configuration page had the number of retry attempts which was set to 3 (I guess by default). I changed it to one and ran a test and it worked as expected.
Many many thanks for the link.
:exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark::exclamationmark:
October 9, 2008 at 1:24 pm
October 10, 2008 at 12:20 pm
One learns something new everyday, thanks Luke
It wasn't far from my assumption that it's a success if Database Mail delivers to SMTP, period
However, if there's SMTP errors (such as incorrect address or authentication), Database Mail does log it in the error log, which is helpful
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply