sql server 2005 database mail error (The operation has timed out)

Question

I have configured Database Mail for sending newsletter emails from sql database. I have a stored procedure that selects the emails of the subscribers and send them the mails like this:

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'ProfileName',
@recipients = @email,
@subject = @thesubject,
@body = @themessage,
@body_format = 'HTML' ;

The account used for sending is working fine coz when I sent the test mail after configuring the Database Mail it was sent successfully but the problem is sending more than one mail.

Currently there are 50 emails in the table after executing the stored procedure I checked the msdb tables sysmails_event_log, sysmails_faileditems and sysmails_sentitems, I found that only the 1st 12 emails were sent and the rest failed and the error message in the event_log was:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-12-01T16:55:11). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )

I checked the emails no. 13 and laters and I didn't find anything wrong with them, so I tried executing the procedure again, this time it sent to the 1st 14 email and the rest failed with the same error so I changed the procedure and made it select the mails starting from mail no. 15 (as if it will send from record 15 to 50) and tested it, this time it sent to the 1st 13 email, I selected from 27 to 50 and executed, again it sent to the 1st 12.

I don't understand what's going on here what's this behavior of sending mails only in the range of 12-14 mails at a time.

Can anyone help me on this?

Thanks in advance

Answer 1

Where is the SMTP server located? Have you checked to see if you can fire more than 12 e-mails at a time through that SMTP server without SQL Server being involved at all? It looks like the problem is not SQL Server but some kind of policy on the SMTP server. There may be some kind of abuse prevention on the firewall or other network appliances in between the database server and the SMTP server as well. Have you considered setting up SMTP services on the database server, then setting up Database Mail so that it uses the local SMTP server instead of going over the network (and potentially running into the policy or policies alluded to above)? Of course you should set up SMTP securely, making sure that only connections from the local machine are allowed.