Database Mail Troubleshooting
I use the following scritps to troubleshoot database mail issues.
–Get a list of email sent items
SELECT * FROM dbo.sysmail_sentitems
–Another version of the script to get a list of email sent items
USE msdb
SELECT sent_status, *
FROM sysmail_allitems
order by send_request_date desc
–Check to see if broker service is enabled
SELECT is_broker_enabled FROM sys.databases WHERE name = ‘msdb’;
–Check if database mail service is running
EXECUTE msdb.dbo.sysmail_help_status_sp
For SQL 2016 – you will need to have .net 3.5 installed on the server for db mail to work
EXEC msdb.dbo.sysmail_help_configure_sp;
–Get the name of the database profile and account name
EXEC msdb.dbo.sysmail_help_account_sp; –this will show the db mail account name
EXEC msdb.dbo.sysmail_help_profile_sp; –this will show the db mail profile name
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;