September 30, 2011 at 9:05 am
We have a SQL 2008 EE, SP2 hoss of a server and have never had an issue before with the sending/receiving of email.
I woke up this morning and checked my email to find that I was missing all of my standard nightly emails from the servers (EOD checks, job failures/successful, and so forth.
When checking the logs, everything seems ok...and I see messages like:
Date9/30/2011 8:36:56 AM
LogJob History (DAILY - Send Unanswered Call Report to CallAsstSup@mycompany.com)
Step ID1
ServerNAS2-DBR
Job NameDAILY - Send Unanswered Call Report to CallAsstSup@mycompany.com
Step NameExecute dbo.F1_Phone_RPT_UnAnswered_Send_Email
Duration00:02:39
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: mycompany\F1Onsitelog. Mail queued. [SQLSTATE 01000] (Message 0). The step succeeded.
However, no one's received the email. I reran the job, same result - successful, mail queued...but no email.
Dove deeper and went into the Database Mail setup and verified it was using the correct profile and server (which is exchange by the way) - Everything looks okay there as well. Send myself a test message, no email.
It's always been my inderstanig that SQL Server using SMTP and relays the mail through exchange...(correct me if I'm wrong on this assumption)....so if SQL Agent jobs are sending/queuing emails but they're either never being sent/delivered that the issue should be indicative of something going wrong with the SMTP queue...or an issue with Exchange? Important to note that emails are being received via outlook, just not the ones from this particular SQL server.
Forgive me if my explanation here is rather "flakey" but I've got some many issues going on here this morning and am ready to pull my hair out!!!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 30, 2011 at 9:27 am
run this query to check for mail sent but that failed;
there's a description column with any errors returned formt eh mails erver when it was sent; stuff like relaying not permitted, mailbox full, lots of different issues may show up there:
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
if you see the messages in there, you know the issue is outside of the SQL server database mail.
if you DON't see the messages, then there's a problem witht he service broker not processing messages...i don't have a diagnostic for that, but other posters have bounced their server by stopping and starting the SQL service to fix similar issues.
Lowell
September 30, 2011 at 9:34 am
Thanks for the quick reply Lowell...no errors found though...
Had a brain fart there when I posted my comment about the SMTP service...went into look to check on the SMTP service on the SQL server and noticed that it wasn't even installed on the machine?
How does SQL send email if it doesn't have SMTP installed??? Does it just relay via exchange?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 30, 2011 at 9:39 am
MyDoggieJessie (9/30/2011)
Thanks for the quick reply Lowell...no errors found though...Had a brain fart there when I posted my comment about the SMTP service...went into look to check on the SMTP service on the SQL server and noticed that it wasn't even installed on the machine?
How does SQL send email if it doesn't have SMTP installed??? Does it just relay via exchange?
nope nothing related to SMTP needs to be installed.
when you setup the profile, you told SQL where the mail server is...
you just end up sending a properly organized packet of info to that mail server, and that mails erver, which might be in New Hampshire for all you know, and the mail process ill report stuff like it could not find connect to the server, or, if it did connect, it will report any errors returned from the mail server itself.
behind the schenes, it's just using the .net framework to send the email, i believe.
if it's not in the failed mail, i'd bounce the server if you can and try again....it's not even getting to the service broker.
Lowell
September 30, 2011 at 9:44 am
I don't get it...I can't find any errors in the windows event or SQL logs. Everything says successful. Surely there's an error somewhere???
You're certain the only thing to try is a reboot? Would restarting the MSSQL Server Service been enough or does the entire server need to be bounced?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 30, 2011 at 9:59 am
MyDoggieJessie (9/30/2011)
I don't get it...I can't find any errors in the windows event or SQL logs. Everything says successful. Surely there's an error somewhere???You're certain the only thing to try is a reboot? Would restarting the MSSQL Server Service been enough or does the entire server need to be bounced?
i cannot find it now, but someone with a very similar issue could not stop the service, but as soon as he was able to a couple of days later, he reported that dbmail woirked again, so he just stoppe dthe service, and did not reboot.
Lowell
September 30, 2011 at 10:16 am
I'd hate to just "restart" as being the end all fix for something I haven't yet figured out the solution to (not to mention the "powers that be" in the company don't like it when the solution to an issue is rebooting the server 🙂
I'm trying to stop and start the sysmail services on the server but the stop command just sits there executing...
exec msdb..sysmail_stop_sp
exec msdb..sysmail_start_sp
Found out is was being blocked by this process:
msdb.dbo.sp_readrequest, Application :: DatabaseMail - DatabaseMail - ID<119804>, last activity was 9/29/2011 (which was about when email stopped sending).
I killed this process and the sysmail_stop_sp completed successfully. So I waited a minute and then restarted it...it restarted successfully as well, but still no email 🙁
I run the query you sent (slightly modified) and also get no errors:
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 > = CAST(DATEDIFF(dd,0 ,GETDATE()) AS DATETIME)
ORDER BY send_request_date DESC
I have verified that the service borker is enabled for the MSDN database (which I am under the assumption is the proper place for it to be)
I found there are 1,170 emails which are unsent by running:
SELECT *
FROM sysmail_unsentitems where send_request_date > = '2011-09-29 00:00:00.577'
order by send_request_date desc
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 30, 2011 at 10:21 am
I also saw this in the SQL Event Log:
The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following: 'The service queue "ExternalMailQueue" is currently disabled.'
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 30, 2011 at 11:15 am
Rebooting the server resolved the issue...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 3, 2011 at 1:17 am
Yup... We all hate to restart the server but most of the times it works well. :laugh:
October 3, 2011 at 2:38 am
I too had the same issue & I had also posted the same on SQL Central. After a weeks troubleshooting I finally had to restart the server to resolve the issue, and it worked. But still have no clue as to what was the issue???????
Is there any other alternative than restarting the server! Restarting the server is not a viable option for 24/7 production databases.
October 3, 2011 at 8:01 am
Although I am not 100% certain of this, I believe the issue to have been that the service broker at the OS-level was hung...which from my understanding is what relays the mail from SQL server to the exchange server. I'll look through the articles I read through later and post them here for your reading...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 21, 2016 at 1:30 pm
Same issue here.
I ran
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
but the result is nothing & Also I ran
select * from sysmail_unsentitems
also nothing. How to trouble shoot the issue. The test email is working fine
April 21, 2016 at 2:54 pm
it's been a while on this thread, but i had this occurred to me twice since i last followed up on this;
here's some notes i took about the service broker queue being broken or stuck,and I've had to clear the queue myself as a result. stopping and starting the SQL service accomplishes the same thing, but this is a better way to resolve the issue without bouncing the server, which i cannot do on production except under controlled and scheduled maintenance times.
my diagnostic: a simple query to see if anything is in the queue:
SELECT COUNT(*) FROM msdb.dbo.ExternalMailQueue
As I understand it the queue named ExternalMailQueue in msdb would have items in it just for the milliseconds between when an email is sent to the queue,and when it is processed; if the queue has items, it better be receiving items at that time;
what you are probably seeing is the queue is stuck in that case.
i found this snippet on the internet(sorry, no credit to where i got it from), which effectively clears the queue and makes it ready to receive again, and clears out that sutck state we are discussing here.
ALTER QUEUE ExternalMailQueue WITH STATUS = ON
DECLARE @ch UNIQUEIDENTIFIER;
DECLARE @message_type NVARCHAR(256);
DECLARE @message_body NVARCHAR(MAX);
DECLARE @ctr BIGINT;
DECLARE @ctr2 BIGINT;
WHILE (1=1)
BEGIN
RECEIVE @ch = conversation_handle, @message_type = message_type_name FROM msdb.dbo.ExternalMailQueue;
SET @ctr2 = @ctr2 + 1;
SET @ctr = (SELECT COUNT(*) FROM msdb.dbo.ExternalMailQueue);
IF @ctr = 0
BREAK;
END;
aside from that, i have my mail servers set to try to resend only a single time.
the reason for that is in situations where a dbmail message that has two emails, one good email address and one bad one, the guy with the good email gets the email twice.
--#################################################################################################
--retries are defaulted to a single send attempt, HPP mails ervers hiccup too much, changing to 3
--#################################################################################################
EXECUTE [msdb].[dbo].[sysmail_configure_sp]
@parameter_name ='AccountRetryAttempts',
@parameter_value = 1;
so i'll investigate the issue, fix it if i can, and finally decide if i need to resend failed emails with code like this:
this simplesnippet resends anything that failed within a given time frame(one day as seen here by the parameter values)
declare @mailitem_id int,
@sendmailxml varchar(max),
@rc int,
@StartDate datetime,
@EndDate datetime
-- get the dates to use in the query
set @StartDate = convert(datetime, convert(char(8), GetDate(), 112))
set @EndDate = @StartDate + 1
declare cFailedMail cursor for
SELECT mailitem_id
FROM [msdb].[dbo].[sysmail_faileditems]
WHERE send_request_date >= @StartDate
AND send_request_date < @EndDate
open cFailedMail
fetch next from cFailedMail into @mailitem_id
while @@fetch_status = 0 begin
-- Create the primary SSB xml maessage
SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
+ CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'
-- Send the send request on queue.
EXEC @rc = msdb.dbo.sp_SendMailQueues @sendmailxml
IF @rc <> 0
BEGIN
RAISERROR(14627, 16, 1, @rc, 'send mail')
END
fetch next from cFailedMail into @mailitem_id
end
close cFailedMail
deallocate cFailedMail
Lowell
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply