Export (0) Print
Expand All
6 out of 11 rated this helpful - Rate this topic

Troubleshooting Database Mail: General steps

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.

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.

  2. Click Surface Area Configuration for Features.

  3. Under MSSQLSERVER, expand Database Engine, and then click Database Mail.

  4. Ensure that Enable Database Mail stored procedures is selected, and then click Apply.

  5. Exit the SQL Server Surface Area Configuration tool.

  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 = '<database user>'
    
  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.

  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.

ms187540.note(en-US,SQL.90).gifNote:
Use the length column in the result set of sysmail_help_queue_sp to determine the number of e-mails in the Mail queue.

  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 ;
    
ms187540.note(en-US,SQL.90).gifNote:
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.

  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.

ms187540.note(en-US,SQL.90).gifNote:
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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.