Quick Scan Report – Failed Database Mail

Understanding Database Mail in SQL Server

Database Mail is a powerful and flexible component of SQL Server that allows you to send email messages directly from your SQL Server instance. This feature is particularly useful for automating notifications, alerts, and reports, making it an essential tool for database administrators and developers.

What is Database Mail?

Database Mail is a high-performance, highly reliable solution for sending email messages from the SQL Server Database Engine. It uses SMTP (Simple Mail Transfer Protocol) to send email messages to any standard email recipient, such as an administrator or user. Database Mail is designed to be easy to configure, secure, and scalable.

Key Features of Database Mail

  1. SMTP Protocol: Database Mail uses the standard SMTP protocol, which ensures compatibility with most email systems.
  2. Security: It integrates with SQL Server security to prevent unauthorized access. Only members of the DatabaseMailUserRole in the msdb database can send emails.
  3. Scalability: Capable of handling a large volume of messages without impacting database performance. It uses an asynchronous architecture, meaning it queues messages and sends them in the background.
  4. Logging and Auditing: Every email sent through Database Mail is logged in the msdb database, providing an audit trail that helps in troubleshooting and monitoring.
  5. Multiple Profiles and Accounts: Supports multiple mail profiles and accounts, allowing for flexibility in email configuration and management.

Troubleshooting

If your SQL Server is attempting to send database mail, and the send process fails, it may not be entirely obvious that the mail failed.

You can use the following query to see what mail has been sent, or has been attempted to be sent from your SQL Server;


SELECT TOP 100 * 
 FROM msdb.dbo.sysmail_allitems WITH (NOLOCK)
 order by send_request_date desc;

The Quick Scan Report detects Failed Database Mail.

After you have fixed the errors causing database mail to fail, you can clean up the log and the history of the mail items with the following commands. The first one cleans the log, and the second one cleans the history of mailitems.

EXECUTE msdb.dbo.sysmail_delete_log_sp;

DECLARE @GETDATE datetime;

SET @GETDATE = GETDATE();  

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;

Related tables for troubleshooting:

msdb.dbo.sysmail_faileditems
msdb.dbo.sysmail_sentitems
msdb.dbo.sysmail_allitems
msdb.dbo.sysmail_profile
msdb.dbo.sysmail_profileaccount
msdb.dbo.sysmail_account
msdb.dbo.sysmail_log

Related Links: