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)
  • 2 comments:

    Post a Comment

    Amazon Contextual Product Ads

    Popular Posts