Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Mail Frustrations Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 5:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:29 AM
Points: 916, Visits: 2,878
We have a new server with SQL Server 2012. Database mail has been configured and appears to be working. I am seeing some strange behaviour though and I don't know if it is an issue with SQL Server, Exchange or the infrastructure. This is what I am seeing:

1. Create a cursor to send myself 10 emails.
DECLARE @i INT = 1;

WHILE @i <= 10
BEGIN
EXEC msdb..sp_send_dbmail
@profile_name = 'my_profile',
@recipients = 'me@mydomain.co.za',
@subject = 'Test',
@body = 'Testing Mail';

SET @i = @i + 1;
END;

2. Monitor the msdb mail tables
SELECT  'Unsent', COUNT(*)
FROM msdb..sysmail_unsentitems
WHERE sent_date > '2012-11-19 14:15'
UNION ALL
SELECT 'Failed', COUNT(*)
FROM msdb..sysmail_faileditems
WHERE sent_date > '2012-11-19 14:15'
UNION ALL
SELECT 'Sent', COUNT(*)
FROM msdb..sysmail_sentitems
WHERE sent_date > '2012-11-19 14:15'
UNION ALL
SELECT 'All', COUNT(*)
FROM msdb..sysmail_allitems
WHERE sent_date > '2012-11-19 14:15'

For about 1 minute after I have run my code I see NOTHING in these tables.
Unsent 0
Failed 0
Sent 0
All 0

Then I have 10 entries in the unsent table. They all have a sent_status of retry.
Unsent 10
Failed 0
Sent 0
All 10

After a few more minutes I see they have all failed.
Unsent 0
Failed 10
Sent 0
All 10

However, I have 20 emails sitting in my inbox. So somehow SQL Server sends me two copies of each email then reports them as failed.

I also have 20 errors in the Database Mail Log, all of which say:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2012-11-19T14:36:58). Exception Message: Cannot send mails to mail server. (The operation has timed out.).

And I also have the following error in the SQL Server Log:
An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.InternalMailQueue' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.

Any ideas?




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1386328
Posted Thursday, November 22, 2012 7:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 175, Visits: 547
I successfully tried you test.
(No column name)	(No column name)
Unsent 0
Failed 0
Sent 11
All 11


Have you tried dropping the profile / account and recreating them?
Can you give it a go via another smtp server?




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1387909
Posted Tuesday, May 7, 2013 7:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 1, 2014 12:09 AM
Points: 1, Visits: 139
Hi did you find out what caused the issue?
Post #1450128
Posted Tuesday, May 14, 2013 6:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 920, Visits: 702
please check whether you have configured correctly database mail account in the same profile?

Abhijit - http://abhijitmore.wordpress.com
Post #1452536
Posted Tuesday, May 21, 2013 5:13 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:29 AM
Points: 916, Visits: 2,878
Jason Nel (5/7/2013)
Hi did you find out what caused the issue?

The Exchange administrators change something and the problem was resolved. I'm not sure what they changed as it was at a clients site.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1454924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse