Issue with Database Mail

Question

Issue with Database Mail

Hakuna Matata

SSCertifiable

Points: 5577
More actions
September 7, 2011 at 3:14 am

#243500

Hi,
I have scheduled multiple jobs using Database Mail on our production server. From 2nd of September we have not received any mails. I did check the Logs, the jobs are getting executed, but when I check the Database Mail logs, there are no logs after September 1st. I restarted the SQL Server Agent too. What could be the issue?
We are using SQL Server 2008 Standard Edition.
Regards,
Nithin

Viewing 15 posts - 1 through 15 (of 29 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

(: Hakuna Matata 🙂 (9/7/2011)
Hi,
I have scheduled multiple jobs using Database Mail on our production server. From 2nd of September we have not received any mails. I did check the Logs, the jobs are getting executed, but when I check the Database Mail logs, there are no logs after September 1st. I restarted the SQL Server Agent too. What could be the issue?
Regards,
Nithin

Right click the dbmail --> send test mail--> then check what error its throw

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Shivrudra SSCrazy Points: 2835 More actions · Answer 2

1. Telnet your mail server with smtp port and check whether mail server available or not

2. confirm once again whether database mail is started or not with EXEC msdb.dbo.sysmail_help_status_sp

3.if yes, manually execute the send mail command and check whether it is working or not

4.for more check the mail log with SELECT * FROM msdb.dbo.sysmail_event_log

Regards,
Shivrudra W

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 3

Tried all the 4 options, Everything is working fine. Tried sending a test mail. No entry in the Database Mail logs.

Lowell SSC Guru Points: 323518 More actions · Answer 4

check your procedure that is supposed to be sending stuff;

if it has some sort of condition, and the condition is not met, the mail code might not be called at all;

you might want to post the step of one of the jobs that is supposed to send email for a bit of peer review; maybe we can help.

my jobs are typically like this

IF EXISTS(SELECT DataToSend...)

BEGIN

'SELECT DataToSend....

'send mail

END

so the condition is critical.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 5

Even my test mails are not getting delivered. And there is no log for that too. There is no entry in Database Mail log after 01 SEP.

The Jobs are getting executed, but not receiving Mails for the jobs run.

Lowell SSC Guru Points: 323518 More actions · Answer 6

(: Hakuna Matata 🙂 (9/7/2011)
Tried all the 4 options, Everything is working fine. Tried sending a test mail. No entry in the Database Mail logs.

does this return any results fromt he failed mails?

SELECT

err.[description],

fail.*

FROM [msdb].[dbo].[sysmail_event_log] err

inner join [msdb].[dbo].[sysmail_faileditems] fail

ON err.mailitem_id = fail.mailitem_id

WHERE send_request_date >= '20110901 00:00:00.000'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 7

Nope!! This does not return any results for dates 01/09 to 07/09. But I do get results for dates less than 01/09.

Lowell (9/7/2011)
(: Hakuna Matata 🙂 (9/7/2011)
Tried all the 4 options, Everything is working fine. Tried sending a test mail. No entry in the Database Mail logs.
does this return any results fromt he failed mails?
SELECT
err.[description],
fail.*
FROM [msdb].[dbo].[sysmail_event_log] err
inner join [msdb].[dbo].[sysmail_faileditems] fail
ON err.mailitem_id = fail.mailitem_id
WHERE send_request_date >= '20110901 00:00:00.000'

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 8

When I try sending a mail manually, I get the message "Mail Queued". But there is no log in the Database mail for mail being sent or for an error.

Shivrudra SSCrazy Points: 2835 More actions · Answer 9

Recreate the profile once again .....restart the agent and check

Regards,
Shivrudra W

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 10

I have tried this option too. Yet the same problem.

Shivrudra (9/8/2011)
Recreate the profile once again .....restart the agent and check

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 11

When I tried the following Query

SELECT * FROM msdb.dbo.sysmail_allitems

I get the values mentioned below for all the mails that were not delivered.

sent_account_id : NULL

sent_status : unsent

sent_date : NULL

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 12

whjat kind of authentication you are using for this.

Use Basic authentication while creating database mail profile and check that that account has right on the SMTP server to send the mail and check whether that account is locked or not.

check these thing and let me know..

Regards

Ashok

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 13

We are using Basic Authentication. I have tried 3 accounts that have rights on SMTP server. None of these accounts are locked.

ashok.faridabad1984 (9/8/2011)
whjat kind of authentication you are using for this.
Use Basic authentication while creating database mail profile and check that that account has right on the SMTP server to send the mail and check whether that account is locked or not.
check these thing and let me know..
Regards
Ashok

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 14

Does the Database Mail Service need the SQL Server Browser Service? Right now the state of browser service is stopped.