Skip to main content

Troubleshooting Database Mail: General steps

SQL Server 2008 R2

Troubleshooting Database Mail involves checking the following general areas of the Database Mail system. These procedures are presented in a logical order, but can be evaluated in any order.

To determine if Database Mail is enabled

  1. In SQL Server Management Studio, connect to an instance of SQL Server by using a query editor window, and then execute the following code:
    sp_configure 'show advanced', 1; 
    In the results pane, confirm that the run_value for Database Mail XPs is set to 1.
    If the run_value is not 1, Database Mail is not enabled. Database Mail is not automatically enabled to reduce the number of features available for attack by a malicious user. For more information, see Understanding Surface Area Configuration.
  2. If you decide that it is appropriate to enable Database Mail, execute the following code:
    sp_configure 'Database Mail XPs', 1; 
  3. To restore the sp_configure procedure to its default state, which does not show advanced options, execute the following code:
    sp_configure 'show advanced', 0; 

To determine if users are properly configured to send Database Mail

  1. To send Database Mail, users must be a member of the DatabaseMailUserRole. Members of the sysadmin fixed server role and msdb db_owner role are automatically members of the DatabaseMailUserRole role. To list all other members of the DatabaseMailUserRole execute the following statement:
    EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
  2. To add users to the DatabaseMailUserRole role, use the following statement:
    sp_addrolemember @rolename = 'DatabaseMailUserRole'
       ,@membername = '';
  3. To send Database Mail, users must have access to at least one Database Mail profile. To list the users (principals) and the profiles to which they have access, execute the following statement.
    EXEC msdb.dbo.sysmail_help_principalprofile_sp;
  4. Use the Database Mail Configuration Wizard to create profiles and grant access to profiles to users.

To confirm that the Database Mail is started

  1. The Database Mail External Program is activated when there are e-mail messages to be processed. When there have been no messages to send for the specified time-out period, the program exits. To confirm the Database Mail activation is started, execute the following statement.
    EXEC msdb.dbo.sysmail_help_status_sp;
  2. If the Database Mail activation is not started, execute the following statement to start it:
    EXEC msdb.dbo.sysmail_start_sp;
  3. If the Database Mail external program is started, check the status of the mail queue with the following statement:
    EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
    The mail queue should have the state of RECEIVES_OCCURRING. The status queue may vary from moment to moment. If the mail queue state is not RECEIVES_OCCURRING, try stopping the queue using sysmail_stop_sp and then starting the queue using sysmail_start_sp.
Use the length column in the result set of sysmail_help_queue_sp to determine the number of e-mails in the Mail queue.

To determine if problems with Database Mail affect all accounts in a profile or only some accounts

  1. If you have determined that some but not all profiles can send mail, then you may have problems with the Database Mail accounts used by the problem profiles. To determine which accounts are successful in sending mail, execute the following statement:
    SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems;
  2. If a profile which is not working does not use any of the accounts listed, then it is possible that all the accounts available to the profile are not working properly. To test individual accounts, use the Database Mail Configuration Wizard to create a new profile with a single account, and then use the Send Test E-Mail dialog box to send mail using the new account.
  3. To view the error messages returned by Database Mail, execute the following statement:
    SELECT * FROM msdb.dbo.sysmail_event_log;
Database Mail considers mail to be sent, when it is successfully delivered to a SMTP mail server. Subsequent errors, such as an invalid recipient e-mail address, can still prevent mail from being delivered, but will not be contained in the Database Mail log.

To configure Database Mail to retry mail delivery

  1. If you have determined that the Database Mail is failing because the SMTP server cannot be reliably reached, you may be able to increase your successful mail delivery rate by increasing the number of times Database Mail attempts to send each message. Start the Database Mail Configuration Wizard, and select the View or change system parameters option. Alternatively, you can associate more accounts to the profile so upon failover from the primary account, Database Mail will use the failover account to send e-mails.
  2. On the Configure System Parameters page, the default values of 5 times for the Account Retry Attempts and 60 seconds for the Account Retry Delay means that message delivery will fail if the SMTP server cannot be reached in 5 minutes. Increase these parameters to lengthen the amount of time before message deliver fails.
When large numbers of messages are being sent, large default values may increase reliability, but will substantially increase the use of resources as many messages are attempted to be delivered over and over again. Address the root problem by resolving the network or SMTP server problem that prevents Database Mail from contacting the SMTP server promptly.
You must be a member of the sysadmin fixed server role to troubleshoot all aspects of Database Mail. Users who are not members of the sysadmin fixed server role can only obtain information about the e-mails they attempt to send, not about e-mails sent by other users.


Popular posts from this blog

Using SSRS web services to render a report as a PDF

I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF. The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1.Create a user on your AD domain with the least amount of privileges (say ReportUser) 2.Give this account browse access on the reporting server for the desired reports. 3.To get this working in visual studio 2010 (I am using the Premium edition) Right click on your project and add a service reference to the above web service URL. (YourSSRSServer) 4.Once the above is done.…

Integrating MIGS eGate Payment gateway using C# for Oz businesses (Server-Hosted Payment method)

If you ever wanted to include the eGate payment gateway in your ASP.Net site and want to save yourself the time in creating the class, here’s how to do it.
First things first you will need a merchant account with ANZ who will give you the following ·Merchant Account ·User name ·password The details could be downloaded from the ANZ web site I am doing this for an Aus base client thus the process might be different to your own country. Please check with the issuing bank for details.
Have a read of the Merchant Admin guide. Login to the merchant account and create an operator ID, Login as the operator and obtain the access code. I will not going to the details as this is all documented in the admin guide.
What you will need for the implementation would be vpc_AccessCode vpc_Merchan MIGS gateway URL:

The site has numerous examples of the PHP, HTTP post and Asp examples. The class was based on …

Creating a Link Server to MYSQL from MSSQL

There might be situations where you need to apply CRUD  operations (
Create,Read,Update,Delete) on data residing in a MYSQL server database from SQL server. In order to do this you will need to create a link service from SQL server to MYSQL. The Steps are outlined below:

1. Install the MYSQL ODBC driver on the server with the MSSQL server installation.
2. Enter details for the MYSQL ODBC settings.
3. Create a link service to MYSQL using SSMS ( SQL Server Management Studio)
4. You will need an user account on the mysql server which can perform CRUD operations
 along with enable remote connections to the MYSQL installation. (See Troubleshooting steps below)
4. Test the connection.

Install MYSQL ODBC Driver Download  ( and install the latest MYSQL ODBC driver from the MYSQL server site. Select the version which corresponds to your installation of MSSQL usually the 64bit version. Note This needs to be done on the server with the MSSQL installed…