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:
- The service broker must be running
- The e-mails should not be failing to be delivered to the SMTP server
To check that the Database Mail service broker is running:
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:
While the service is running there are two queues that Database Mail uses. These can be checked using the following SQL:
The following SQL will provide a count of e-mails based on their status:
1 SELECT 2 CASE 3 WHEN SMAI.sent_status IS NULL THEN 'Total' 4 ELSE SMAI.sent_status 5 END AS StatusDesc 6 , COUNT(SMAI.sent_status) 7 , MIN(SMAI.send_request_date) AS FirstSendRequestDate 8 , MAX(SMAI.send_request_date) AS LastSendRequestDate 9 FROM msdb..sysmail_allitems SMAI 10 GROUP BY 11 ROLLUP(SMAI.sent_status) 12 ;
If there are any with a status of ‘failed’ then the next step is to identify the cause of the failure:
1 SELECT 2 SMFI.mailitem_id 3 , SMFI.subject 4 , SMFI.send_request_date 5 , SMEL.description 6 FROM msdb..sysmail_faileditems SMFI 7 JOIN msdb..sysmail_event_log SMEL ON (SMEL.mailitem_id = SMFI.mailitem_id) 8 ;
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.