Sql-server – DatabaseMail didn’t start when an email is queued

database-mailsql serversql-server-2005

This is about SQL Server 2005. I have configured SQL agent jobs to send an email when it failed. So far I have received email reliably.

However, I have an weird problem where an email would get queued but not delivered immediately. As an example, a job failed on April 1 at 9 AM. When I query

SELECT * FROM dbo.sysmail_mailitems

It will show the error mail has a send_request date on 2015-04-01 09:01:30.000, however, I just received the email today, and the sent_date is shown as 2015-04-02 10:10:51.000.

I checked the database mail log, and the database mail process did NOT start on 2015-4-1. It is only started on 2015-04-02 10:10:51.000, and at that time I open the Database Mail dialog box (I did not change anything – I just opened it to check a few settings).

The database mail log (sysmail_event_log)

2015-03-22 01:00:18.597 DatabaseMail process is started
2015-03-22 01:10:21.013 DatabaseMail process is shutting down
2015-04-02 10:10:50.393 DatabaseMail process is started
2015-04-02 10:20:53.960 DatabaseMail process is shutting down

The previous job error was on 3/22 and the email was sent properly. The latest error on 4/1 didn't.

The server is not under stressed, and there is no error message in the server log. It is just the Database Mail process did not start even when a mail is queued for some reason.

Summary:

Expected behaviour:

  1. Job failed
  2. Agent queue an email
  3. Databasemail process starts
  4. Email sent
  5. Databasemail process stops

Actual behaviour:

  1. Job failed
  2. Agent queue an email
  3. Databasemail process did not start immediately
  4. Databasemail process decided to start after 2 days for unknown reason
  5. Email sent, 2 days late
  6. Databasemail process stops

Best Answer

see what happens when you try to send an email using powershell instead.

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql" 
$body = "test email from ccatsql " 
$smtp.Send($emailFrom, $emailTo, $subject, $body)

and checked which .Net versions you have installed

the problem might be in the .NET. Check the windows\applications error log. I had a similar problem (see the question here) then I installed .NET 3.5 and all started to work nicely.

enter image description here

SQL Server 2016 - Database Mail doesn't work without .NET 3.5

How can I see the current Database Mail configuration?

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

EXEC msdb.dbo.sysmail_help_account_sp

Have a look at this question and answer: Comprehensive troubleshooting of DatabaseMail

Related Question