Troubleshooting SQL Server Send Email

Sending Email

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'ProfileName01',  
    @recipients = 'user@test.com',  
    @body = 'Test from database',  
    @subject = 'Test from database' ;  

EXEC msdb.dbo.sp_send_dbmail
@recipients = N'user@test.com', 
@body_format = 'HTML',
@body = '<table><tr><td>test</td></tr></table>',
@subject = 'Test from database',
@profile_name ='ProfileName01'

-- This one told me that Database Mail was started
EXEC msdb.dbo.sysmail_help_status_sp;
 
-- Here I learned that there were 5 items queued and the last times I tried sending mail
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
 
-- This confirmed none of the email was sent
SELECT * FROM msdb.dbo.sysmail_sentitems;
SELECT * FROM msdb.dbo.sysmail_unsentitems;
 
-- Is Service Broker enabled? It has to be to send mail
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
 
-- I tried stopping and restarting the Database Mail exe
EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_start_sp;

SELECT * FROM msdb.dbo.sysmail_allitems

--sent - The mail was sent.--> If sent_status is "sent" the mail is sent and you need to check is everything OK with mail server.
--unsent - Database mail is still attempting to send the message.
--retrying - Database Mail failed to send the message but is attempting to send it again.
--failed - Database mail was unable to send the message.

-- Email Log
SELECT
er.log_id AS [LogID],
er.event_type AS [EventType],
er.log_date AS [LogDate],
er.description AS [Description],
er.process_id AS [ProcessID],
er.mailitem_id AS [MailItemID],
er.account_id AS [AccountID],
er.last_mod_date AS [LastModifiedDate],
er.last_mod_user AS [LastModifiedUser]
FROM
msdb.dbo.sysmail_event_log er
ORDER BY
[LogDate] DESC

Sources:

SQL Server 2016 – Database mail queued but not sending