1,已发送的邮件的状态
Using msdn.dbo.sysmail_allitems to check the mail status in database mail queue
There's an column named status inside sysmail_allitems which is helpful to determine the issue.
2,正在发送的邮件状态
select * from msdb.dbo.sysmail_unsentitems
3,没有找到关于这个表的具体解释,可能就是mail queue。正常状态下queue中不应该有阻塞,所以这个表中应该为空。这个queue中的邮件应该会进入sysmail_unsentitems。
select * from msdb.dbo.ExternalMailQueue
如果database mail server出现异常,可以尝试清楚该queue中的数据:
Use MSDB
ALTER QUEUE ExternalMailQueue WITH STATUS = ON
set nocount on
declare @Conversation_handle uniqueidentifier;
declare @message_type nvarchar(256);
declare @counter bigint;
declare @counter2 bigint;
set @counter = (select count(*) from ExternalMailQueue)
set @counter2=0
while (@counter2<=@counter)
begin
receive @Conversation_handle = conversation_handle, @message_type = message_type_name from ExternalMailQueue
set @counter2 = @counter2 + 1
end
如果不久以后queue中再次出现邮件而再次导致mail server异常,则需要进一步考察问题。另外queue使用的是sql server broker service。必要时需要往这方面考虑。