Troubleshooting Database Mail

Here I am giving some step to troubleshoot Database Mail related problems

Cant find sp_send_dbmail procedure
This procedure is in MSDB database.

User does not have permission on sp_send_dbmail
Run
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',
@membername = '' ;

Service broker is needed to activate external program which sends queued mails, else mail will be queued but will not be deliverd.

To check if service broker is enabled we need to run

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

If its not enabled , we need to enable by this query.

ALTER DATABSE MSDB SET ENABLE_BROKER

If database mail procedures are not enabled enable it using surface area configuration

To check status of database mail procedures

EXECUTE dbo.sysmail_help_status_sp ;

To start database mail

EXECUTE dbo.sysmail_start_sp ;

SQL Server is using external application Databasemail90.exe , which connect to server using windows authentication and send mail

We can find it on
MSSQL.3\mssql\binn

Even after this if Database Mail does just queued mails and do not send them.
We have to check by running this application manually. If it sends mails then it means service broker is not starting this application.

Run this code in MSDB

ALTER QUEUE [InternalMailQueue] WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = [dbo].[sp_ExternalMailQueueListener],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER);




ALTER QUEUE [ExternalMailQueue] WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = [dbo].[sp_sysmail_activate],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER)