DatabaseMail的綜合故障排除

我在從我的一台伺服器發送電子郵件時遇到問題。

我想使用DatabaseMail和/或msdb.dbo.sp_send_dbmail

我有其他生產伺服器使用所有相同的 databaseMail 設置和相同的 SMTP 伺服器，它們都工作正常，只有這台特定的伺服器沒有通過 Sql Server 發送電子郵件，我需要找出原因。

我測試的第一件事是與 SMTP 伺服器的連接，為了測試我使用 Powershell 發送電子郵件，它工作正常！！！！

這是關於如何使用 Powershell 發送電子郵件的腳本。

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql" 
$body = "test email from ccatsql " 
$smtp.Send($emailFrom, $emailTo, $subject, $body)

這很好用。

我在該伺服器中遇到的問題是， 當電子郵件排隊時，DatabaseMail 沒有啟動

當我查詢sysmail_event_log時，我沒有看到與我剛剛發送（嘗試）的電子郵件相關的任何事件。

SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc 

當我查詢sysmail_unsentitems時，我可以看到我所有的電子郵件都在那裡，處於某種unsert狀態。

SELECT * FROM msdb.dbo.sysmail_unsentitems 
ORDER BY send_request_date DESC

這是我主要用來測試電子郵件發送的一段程式碼。它顯示瞭如何通過電子郵件發送查詢結果，但在這種情況下，我使查詢變得非常簡單，因為我需要先讓電子郵件正常工作。

-===========================================================================
-- Failed to initialize sqlcmd library with error number -2147467259
-- that was caused because I had left a piece of rubish code from a previous query
-- and internally it was saying: Invalid column name 'sintMarketID'.
-- but I could only see this using the profile.
--==========================================================================


set deadlock_priority high
set transaction isolation level repeatable read

declare @sub varchar(150)
declare @radhe int
select @sub = '-- Number of rows affected by Hare Krishna job run at ' 
              + CAST(convert(datetime, getdate(), 100)AS VARCHAR)
--print @sub


   EXEC msdb.dbo.sp_send_dbmail 
       @profile_name = 'DBA', -- Change profile name accordingly
       @recipients='mmiorelli@xxxxx.co.uk', -- change mail address accordingly
       @subject = @sub,
       @body_format = 'TEXT',
       @importance='High',
       @sensitivity='Private',
       @file_attachments=NULL,
       @execute_query_database='MSDB',
       @query_no_truncate=0,
       @query_result_header = 1,
       @mailitem_id= @Radhe OUTPUT,
       @query= '


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = ''

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select getdate()
''

EXEC(@SQL)

'

有類似的問題沒有結論：

DatabaseMail 程序正在關閉

缺什麼？

我檢查了我安裝了哪些 .Net 版本

然後我安裝了 .NET 3.5 並開始正常工作。

我相信 DatabaseMail 需要 .NET 3.5 的事實（或至少對我而言）記錄不充分。

SQL Server 2016 - 數據庫郵件在沒有 .NET 3.5 的情況下無法工作

如何查看目前的數據庫郵件配置？

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;

EXEC msdb.dbo.sysmail_help_account_sp

當我需要將數據庫郵件設置從一台伺服器複製到另一台伺服器時，下面的這個連結也特別幫助了我：

保存到臨時表中的數據庫郵件配置

我得到了以下腳本，它有助於解決 databasemail 基礎問題。

注意評論並一次執行一步。

USE msdb
GO

-- Check that the service broker is enabled on MSDB. 
-- Is_broker_enabled must be 1 to use database mail.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- Check that Database mail is turned on. 
-- Run_value must be 1 to use database mail.
-- If you need to change it this option does not require
-- a server restart to take effect.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs';

-- Check the Mail queues
-- This system stored procedure lists the two Database Mail queues.  
-- The optional @queue_type parameter tells it to only list that queue.
-- The list contains the length of the queue (number of emails waiting),
-- the state of the queue (INACTIVE, NOTIFIED, RECEIVES_OCCURRING, the 
-- last time the queue was empty and the last time the queue was active.
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'Mail' ;

-- Check the status (STARTED or STOPPED) of the sysmail database queues
-- EXEC msdb.dbo.sysmail_start_sp -- Start the queue
-- EXEC msdb.dbo.sysmail_stop_sp -- Stop the queue
EXEC msdb.dbo.sysmail_help_status_sp;

-- Check the different database mail settings.  
-- These are system stored procedures that list the general 
-- settings, accounts, profiles, links between the accounts
-- and profiles and the link between database principles and 
-- database mail profiles.
-- These are generally controlled by the database mail wizard.

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
--  Check that your server name and server type are correct in the
--      account you are using.
--  Check that your email_address is correct in the account you are
--      using.
EXEC msdb.dbo.sysmail_help_profile_sp;
--  Check that you are using a valid profile in your dbmail command.
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
--  Check that your account and profile are joined together
--      correctly in sysmail_help_profileaccount_sp.
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

-- I’m doing a TOP 100 on these next several queries as they tend
-- to contain a great deal of data.  Obviously if you need to get
-- more than 100 rows this can be changed.
-- Check the database mail event log.
-- Particularly for the event_type of "error".  These are where you
-- will find the actual sending error.
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_event_log 
ORDER BY last_mod_date DESC;

-- Check the actual emails queued
-- Look at sent_status to see 'failed' or 'unsent' emails.
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_allitems 
ORDER BY last_mod_date DESC;

-- Check the emails that actually got sent. 
-- This is a view on sysmail_allitems WHERE sent_status = 'sent'
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_sentitems 
ORDER BY last_mod_date DESC;

-- Check the emails that failed to be sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'failed'
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_faileditems 
ORDER BY last_mod_date DESC



-- Clean out unsent emails
-- Usually I do this before releasing the queue again after fixing the problem.
-- Assuming of course that I don't want to send out potentially thousands of 
-- emails that are who knows how old.
-- Obviously can be used to clean out emails of any status.
EXEC msdb.dbo.sysmail_delete_mailitems_sp  
 @sent_before =  '2017-09-28',
 @sent_status = 'failed';

引用自：https://dba.stackexchange.com/questions/186839