January 25, 2016 at 6:10 am
Hi All,
Today came across an issue. Database mail alerts on job failures was not been sent. Checked the dbo.sysmail_faileditems and found that it was failing with authentication error.
Basically, the password for the account used for dbmail has been expired. We have reset the password and tried sending the test mail. It was successful.
Now question is, I have like more 1000 mail items with failed state and they are not sent/resumed after this fix. Is this behaviour is normal or the fails should have been sent/retry once the pwd is fixed?
if they will not be sent, do we need to clean those from any system tables as anyways will not be sent?
Env:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Regards,
Sam
January 25, 2016 at 6:22 am
vsamantha35 (1/25/2016)
Hi All,Today came across an issue. Database mail alerts on job failures was not been sent. Checked the dbo.sysmail_faileditems and found that it was failing with authentication error.
Basically, the password for the account used for dbmail has been expired. We have reset the password and tried sending the test mail. It was successful.
Now question is, I have like more 1000 mail items with failed state and they are not sent/resumed after this fix. Is this behaviour is normal or the fails should have been sent/retry once the pwd is fixed?
if they will not be sent, do we need to clean those from any system tables as anyways will not be sent?
Env:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Regards,
Sam
the default setting for 'AccountRetryAttempts' is a single retry, and the timing between attemts is something small like a few seconds.
I've used this script below to resend anything that did not send since midnight; so you can kick them off manually:
declare @mailitem_id int,
@sendmailxml varchar(max),
@rc int,
@StartDate datetime,
@EndDate datetime
-- get the dates to use in the query
set @StartDate = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
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
January 25, 2016 at 7:38 am
Thank you Lowell. Will try the workaround 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply