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
ORDER BY log_date DESC
Delete Failed Emails
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_status = ‘failed’ ;
GO
@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
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
