2

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
  • What is the output of SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc and SELECT * FROM msdb.dbo.sysmail_unsentitems ? – Kin Shah Apr 2 '15 at 15:49
  • Version of SQL Server? – Eric Higgins Apr 2 '15 at 16:03
  • @Kin sysmail_unsentitems is now empty, It is shown that the email has a send_request date of 4-1 but the sent_date of 4-2 (more than 24 hours later). – elty123 Apr 2 '15 at 17:38
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.