SQL Server Stops sending database mails - How to Fix and How to resend failed Emails without restarting the SQL Server

Database mail is a nice feature, it is generally intended for internal notifications to DBAs, DBEs, etc.

But sometimes Database Mail feature is taken too far. I come across feedback where production support teams mention that SQL Server stopped sending emails to clients. Well, SQL Server should not be used for sending mass emails and attachments, especially to clients or end users, it does not only impact SQL Server performance but not safe too.

Anyways, principles on a side for this blog.

I assume you already tried these steps and this did not help:

• The recipients' email addresses were checked for correctness.
• The SMTP server was up and running.
• SMTP Server was reachaable, there was no firewall between SQL Server and SMTP server and the port was opened in firewall.

Next, Try these steps:

• Open SSMS:
• Stop Mail Service

USE MSDB
GO
sysmail_stop_sp
GO

•  Start Mail Service

USE MSDB
GO
sysmail_start_sp
GO

This will refresh the Extended Procedure. If this does not help then go to the next step:

• Open SSMS:
• Stop Mail Service

USE MSDB
GO
sysmail_stop_sp
GO

•  Kill DatabaseMail.exe process from Windows Task Manager

• Start Mail Service 

USE MSDB
GO
sysmail_start_sp
GO

• This would also bring up the DatabaseMail.exe process.

Database mail should now work. Any emails awaiting in queue should be picked up though any emails which were failed already will not be sent. Though you can manually resend those emails utilizing metadata from system table "sysmail_faileditems".

declare @recipients varchar(max), @cc varchar(max), @subject nvarchar(max), @body nvarchar(max), @format varchar(10), @attachment varchar(max)

DECLARE failedemails CURSOR FOR

Select recipients, copy_recipients, [subject], body, body_format, file_attachments from msdb.dbo.sysmail_faileditems where send_request_date >= 'If you want to filter with days, enter here'

OPEN failedemails
fetch next from failedemails
into @recipients, @cc, @subject, @body, @format, @attachment
while @@fetch_status = 0
begin EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@copy_recipients = @cc,
@body = @body,
@subject = @subject,
@body_format = @format,
@file_attachments = @attachment;
fetch next from failedemails
into @recipients, @cc, @subject, @body, @format, @attachment
end

close failedemails;
deallocate failedemails;

That's it.