Database Mail is an solution for sending e-mail messages from the Microsoft SQL Server Database Engine.
With Database Mail, Applications can send e-mail messages to Clients/Users and the e-mail messages can contain query results and also files attachments. SysAdmins can also leverage Database Mail for their day-to-day operations.
Database Mail is not enabled by default. Enable Database Mail by using either the Database Mail Configuration Wizard, the sp_configure sp or by using the Surface Area Configuration tool.
Database Mail is designed on a queued architecture that uses Service Broker Technologies. It operates on an Asynchronous delivery.
MSDN:
Database Mail is designed on a queued architecture that uses service broker technologies. When users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and creates a record that contains the e-mail message. Inserting the new entry in the mail queue starts the external Database Mail process (DatabaseMail.exe). The external process reads the e-mail information and sends the e-mail message to the appropriate e-mail server or servers. The external process inserts an item in the Status queue for the outcome of the send operation. Inserting the new entry in the status queue starts an internal stored procedure that updates the status of the e-mail message. Besides storing the sent, or unsent, e-mail message, Database Mail also records any e-mail attachments in the system tables. Database Mail views provide the status of messages for troubleshooting, and stored procedures allow for administration of the Database Mail queue.
Only members of the DatabaseMailUserRole in the msdb database can execute sp_send_dbmail.
Success Emails:
SELECT
s.mailitem_id, p.name as 'profile', s.sent_status, s.subject, s.file_attachments, s.recipients, s.copy_recipients,
s.blind_copy_recipients, s.send_request_date, s.sent_date, s.last_mod_date
FROM msdb.dbo.sysmail_sentitems s
INNER JOIN msdb.dbo.sysmail_profile p on s.profile_id = p.profile_id
ORDER BY s.sent_date DESC
Failed Emails:
SELECT
f.mailitem_id, l.log_id, p.name as 'profile', f.sent_status, f.subject, f.file_attachments, f.recipients,
f.copy_recipients, f.blind_copy_recipients, f.send_request_date, f.last_mod_date, l.description
FROM msdb.dbo.sysmail_faileditems as f
INNER JOIN msdb.dbo.sysmail_profile p on f.profile_id = p.profile_id
LEFT OUTER JOIN msdb.dbo.sysmail_event_log as l ON f.mailitem_id = l.mailitem_id
ORDER BY f.mailitem_id, l.log_id
All Emails:
SELECT * FROM msdb.dbo.sysmail_allitems order by send_request_date desc
Queues:
USE MSDB
GO
Exec sysmail_help_queue_sp @queue_type = 'Mail' ;
USE MSDB
GO
SELECT
*,
casted_message_body = CASE message_type_name WHEN 'X' THEN CAST(message_body AS NVARCHAR(MAX)) ELSE message_body END
FROM [msdb].[dbo].[ExternalMailQueue] WITH(NOLOCK)
sp_configure:
Check sp_configure run_value for Database Mail XPs
Email Event Log:
SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc
Email Config:
SELECT * FROM [msdb].[dbo].[sysmail_configuration]
SQL Server MSDB database Service Broker:
select is_broker_enabled,* from sys.databases
1 msdb
Mail Status:
EXECUTE dbo.sysmail_help_status_sp
Re-Start Mail:
Exec sysmail_stop_sp
Exec sysmail_start_sp
Resources:
Database Mail
SELECT * FROM msdb.dbo.sysmail_configuration
SELECT * FROM msdb.dbo.sysmail_profile
SELECT * FROM msdb.dbo.sysmail_principalprofile
SELECT * FROM msdb.dbo.sysmail_account
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_log
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_attachments
SELECT * FROM msdb.dbo.sysmail_event_log
SELECT * FROM msdb.dbo.sysmail_send_retries
EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
Related: Troubleshooting Azure VM SQL Server Database Mail using SmtpClient class, PowerShell and SendGrid
|
|
|
|
|
|