Post reply

Database Mail Frustrations

  • November 19, 2012 at 5:52 am

    #265008

    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

    WHEREsent_date > '2012-11-19 14:15'

    UNION ALL

    SELECT 'Failed', COUNT(*)

    FROM msdb..sysmail_faileditems

    WHEREsent_date > '2012-11-19 14:15'

    UNION ALL

    SELECT 'Sent', COUNT(*)

    FROM msdb..sysmail_sentitems

    WHEREsent_date > '2012-11-19 14:15'

    UNION ALL

    SELECT 'All', COUNT(*)

    FROM msdb..sysmail_allitems

    WHEREsent_date > '2012-11-19 14:15'

    For about 1 minute after I have run my code I see NOTHING in these tables.

    Unsent0

    Failed0

    Sent0

    All0

    Then I have 10 entries in the unsent table. They all have a sent_status of retry.

    Unsent10

    Failed0

    Sent0

    All10

    After a few more minutes I see they have all failed.

    Unsent0

    Failed10

    Sent0

    All10

    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[/url]

    @SeanPearceSQL

    About Me[/url]

  • November 22, 2012 at 7:58 am

    #1561648

    I successfully tried you test.

    (No column name)(No column name)

    Unsent0

    Failed0

    Sent11

    All11

    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.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • May 7, 2013 at 7:19 am

    #1612973

    Hi did you find out what caused the issue?

  • May 14, 2013 at 6:35 am

    #1614957

    please check whether you have configured correctly database mail account in the same profile?

    Abhijit - http://abhijitmore.wordpress.com

  • May 21, 2013 at 5:13 am

    #1616932

    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[/url]

    @SeanPearceSQL

    About Me[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply