SQL Server Database Mail – Diagnose and Resolve Issues

To Check the mail service status

EXEC msdb.dbo.sysmail_help_status_sp;

To Stop and Restart Mail Queue/ Service

EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_start_sp;

To Check Sent and Unsent Email

SELECT * FROM msdb.dbo.sysmail_sentitems;
SELECT * FROM msdb.dbo.sysmail_unsentitems;

 

To manually send a test email

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Default Profile’,
@recipients = ‘yourame@yourDmain.com’,
@subject = ‘Try #1
@body = ‘This is a try#1’;
Check Mail log
SELECT * FROM msdb.dbo.sysmail_event_log
ORDER BY log_date DESC
Delete Failed Emails
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_status = ‘failed’ ;
GO
Delete Unsent Messages
Exec msdb..sysmail_delete_mailitems_sp @sent_status =‘unsent’
Delete All Emails
DECLARE @GETDATE datetime
SET @GETDATE = GETDATE();
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;
GO
Microsoft Documentation
https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail