Timeout Error - Sending mail through SQL Mail - Service Broker Queue

Question

This is my first question! So I hope I provide enough information:

We are attempting to send around 1000 emails by taking advantage of the SQL Mail Service Broker. We are relatively new to this, but have hit a wall with this problem:

Some Background:

We created a Profile, an Account and a ProfileAccount entries, and enable database mail. We then tested with a few emails and all worked fine. We then created a store procedure that we would call from our project to QUEUE all the emails using msdb.dbo.sp_send_dbmail. This worked well, and we can see all the mail queued up successfully in msdb.dbo.sysmail_mailitems. The Service Broker then fires into action and starts processing the emails.

The Problem:

After sending roughly 90 or so (never the same number) an error is reported many times in the sql event logs

The mail could not be sent to the recipients because of the mail server failure.(Sending Mail using Account 42 (2011-09-19T17:20:09). Exception Message: Cannot send mails to mail server. (The operation has timed out.). Sending Mail using Account 42 (2011-09-19T17:21:59). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

I refered to this website for help: http://www.sqlteam.com/article/how-to-troubleshoot-service-broker-problems

Which made me a little confused, I have run Query Profiler while the Broker is running and all seems fine.

I executed this:

    select * from sys.dm_broker_queue_monitors

which displayed the broker queue with a state of NOTIFIED. This 'NOTIFIED' state seem to imply that the activation sp wasn't working, but the logs don't indicate this, and I checked all the following which didn't give me any clue:

    select * from sys.transmission_queue;
    select * from sys.conversation_endpoints;
    select * from sys.dm_broker_activated_tasks;
    select * from sys.dm_broker_connections;

If I run the following commands:

    EXEC msdb.dbo.sysmail_stop_sp;
    EXEC msdb.dbo.sysmail_start_sp;

the broker starts up again, and the same thing happens.

The windows event log seemed to show the most helpful message, but I'm unsure how to solve it:

    Event Type: Error
    Event Source:   DatabaseMail
    Event Category: None
    Event ID:   0
    Date:       9/19/2011
    Time:       5:18:44 PM
    User:       N/A
    Computer:   _____
    Description:
    There was an error on the connection. Reason: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
    Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

And together with that the if I run this: "select * from dbo.sysmail_log;" I see long list of the timeout errors I showed at the top of this post. I have also discovered that when all these errors occur it closes down the Broker Service (Message Poisoning I think it's called) so therefore I need to run the sysmail_stop_sp and start to get it going again.

Thanks for your help, and I hope I provided enough information.

Charles

Answer 1

I have had a similar problem, but unfortunately never got an answer. In the end, all that I could do was to monitor the QUEUE and when it has crashed just restart it. I used the following stored procedure to do this.

DECLARE @state nvarchar(50),
        @length int,
        @last_activated_time datetime
CREATE TABLE #MailStatusTempTable
(
[queue_type] nvarchar(max),
[length] int,
[state] nvarchar(max),
[last_empty_rowset_time] datetime,
[last_activated_time] datetime
)
INSERT INTO #MailStatusTempTable EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'
SELECT TOP 1 @state = [State],@length=[length],@last_activated_time = [last_activated_time] FROM #MailStatusTempTable
DROP TABLE #MailStatusTempTable
IF (@length>0)
BEGIN
    IF (@state <> 'RECEIVES_OCCURRING')
    BEGIN
        IF (DATEDIFF(minute,@last_activated_time,GETDATE())>5) --ensuring 5 minutes has passed since last activity (your timeout might be different)
        BEGIN
            EXEC msdb.dbo.sysmail_stop_sp
            EXEC msdb.dbo.sysmail_start_sp
        END
    END
END

I hope this is of some help to you. If so, please remember to mark this as the answer!

Kind regards, Willem