Database Mail, Part 5 - Monitoring

Now that Database Mail is set up and can be used it is important to monitor the process. In particular the following issues should be identified as they may have an impact on the effectiveness of any solution that depends on Database Mail:

1. The service broker must be running
2. The e-mails should not be failing to be delivered to the SMTP server

Service Broker

To check that the Database Mail service broker is running:

EXEC msdb..sysmail_help_status_sp

The result of this will be either “RUNNING” or “STOPPED”. If the result is “STOPPED” then the following procedure call will start the service broker:

EXEC msdb..sysmail_start_sp

While the service is running there are two queues that Database Mail uses. These can be checked using the following SQL:

EXEC msdb..sysmail_help_queue_sp

Failures

The following SQL will provide a count of e-mails based on their status:

SELECT
   CASE
      WHEN SMAI.sent_status IS NULL THEN 'Total'
      ELSE SMAI.sent_status
   END AS StatusDesc
,  COUNT(SMAI.sent_status)
,  MIN(SMAI.send_request_date) AS FirstSendRequestDate
,  MAX(SMAI.send_request_date) AS LastSendRequestDate
FROM msdb..sysmail_allitems SMAI
GROUP BY
   ROLLUP(SMAI.sent_status)
;

If there are any with a status of ‘failed’ then the next step is to identify the cause of the failure:

SELECT
   SMFI.mailitem_id
,  SMFI.subject
,  SMFI.send_request_date
,  SMEL.description
FROM msdb..sysmail_faileditems SMFI
JOIN msdb..sysmail_event_log SMEL ON (SMEL.mailitem_id = SMFI.mailitem_id)
;

The ‘description’ field will provide information on the reason for the failure.

That concludes the setup of Database Mail. In future posts I will use Database Mail to enhance the monitoring of SQL Server and to provide a mechanism for providing more detailed information from maintenance plans.