SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Mail Frustrations


Database Mail Frustrations

Author
Message
Sean Pearce
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4186 Visits: 3436
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
Dennis Post
Dennis Post
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 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
Jason Nel
Jason Nel
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 153
Hi did you find out what caused the issue?
Abhijit More
Abhijit More
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2007 Visits: 767
please check whether you have configured correctly database mail account in the same profile?

Abhijit - http://abhijitmore.wordpress.com
Sean Pearce
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4186 Visits: 3436
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search