MS SQL SERVER 2008 Database Mail , sysmail_mailitmes 资料莫名消失.

[鸭梨山大帝]

本人所在公司使用的是MS SQL SERVER 2008中的Database Mail 派信,近几天发现有邮件无法派送出去,准备来查派信的log,结果却发现了另外一个严重的问题.
sysmail_log中有些资料行有的mailitem_id,却不存在与 sysmail_mailitems中.
执行以下语句:

SELECT  l.mailitem_id ,  m.mailitem_id ,  send_request_date ,   m.sent_status ,  r.send_attempts 
FROM    dbo.sysmail_log l
LEFT JOIN dbo.sysmail_mailitems m ON m.mailitem_id = l.mailitem_id
LEFT JOIN dbo.sysmail_send_retries r ON l.mailitem_id = r.mailitem_id
WHERE   l.event_type = 3

发现,有些资料行: l.mailitem_id 有值,而 m.mailitem_id 为NULL.
为了该问题,我监控过一段时间这个查询结果的变化,发现一开始运行上面的语句时,同一mailitem_id资料都是存在与三个表中的, l.mailitem_id , m.mailitem_id , r.mailitem_id 都有值,但是过了一段时间(大概几分钟~十多分钟后) l.mailitem_id 有值, m.mailitem_id , r.mailitem_id 却变成了null,
这些mailitem在表sysmail_mailitems 与sysmail_send_retries
被删除了. 而 sysmail_log中却存在.
这是为什么?是由于某个设定吗?
希望了解的人士提供帮助,非常感谢.
(PS:之前有听到一种说法是说发送成功的邮件就会被删除,但是我发现很多发送成功依然存在与三个表中,实在不解了?)

在MSDN发帖三天,无人理会.
http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/e9ec83f7-33cd-4913-a935-7fcf180d47f8/#6219b3b4-4e4f-4895-a73e-aca2b8edad3e

在stackoverflow发帖半日,无人理会.
http://stackoverflow.com/questions/8275715/the-data-in-sysmail-mailitems-disappeared-with-no-reason-when-i-use-sql-server-2

[中国风]

若要查看 Database Mail 處理的所有訊息使用 sysmail_allitems (Transact-SQL)。
若只要查看失敗狀態的訊息，請使用 sysmail_faileditems (Transact-SQL)。
若只要查看已傳送的訊息，請使用 sysmail_sentitems (Transact-SQL)。

http://technet.microsoft.com/zh-cn/library/ms188023.aspx

所有記錄在msdb.dbo.sysmail_mailitems表
通過以上視圖查看試試

[鸭梨山大帝]

这个是sysmail_allitems视图的源代码:

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO

CREATE VIEW sysmail_allitems
AS
SELECT mailitem_id,
       profile_id,
       recipients,
       copy_recipients,
       blind_copy_recipients,
       subject,
       body,
       body_format,
       importance,
       sensitivity,
       file_attachments,
       attachment_encoding,
       query,
       execute_query_database,
       attach_query_result_as_file,
       query_result_header,
       query_result_width,
       query_result_separator,
       exclude_query_output,
       append_query_error,
       send_request_date,
       send_request_user,
       sent_account_id,
       CASE sent_status 
          WHEN 0 THEN 'unsent' 
          WHEN 1 THEN 'sent' 
          WHEN 3 THEN 'retrying' 
          ELSE 'failed' 
       END as sent_status,
       sent_date,
       last_mod_date,
       last_mod_user
FROM msdb.dbo.sysmail_mailitems
WHERE (send_request_user = SUSER_SNAME()) OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)

GO

都是来源与 msdb.dbo.sysmail_mailitems 的.

我的问题在于,为什么 sysmail_log存在的mailitem_id,而 sysmail_mailitems 中却不存在.
一开始监控的时候,sysmail_mailitems存在某个mailitem_id,但过了几分钟或者十几分钟,就发现该mailitem被删除了,而sysmail_log中一直存在该mailitem_id的记录.
这是为什么?是不是有什么设定?
如果这种情况下如何知道该mailitems被发送成功了?

疑惑.


[Quote=引用 7 楼 roy_88 的回复:]

若要查看 Database Mail 處理的所有訊息使用 sysmail_allitems (Transact-SQL)。
若只要查看失敗狀態的訊息，請使用 sysmail_faileditems (Transact-SQL)。
若只要查看已傳送的訊息，請使用 sysmail_sentitems (Transact-SQL)。

http://technet.microsoft.com/……
[/Quote]

[鸭梨山大帝]

马勒戈壁的草泥马... ...

我找到原因了,原来是之前离职的同仁有开发一个"异想天开","想当然"的JOB在杀微软 sysmail_mailitems里面sent_status=2的资料.

真的是超级想当然啊... ...,你杀资料也杀干净点啊,别到处留尾巴... ...

这个Job在生产环境有运行,我没权限看.而在测试环境Job压根没运行,这样就造成了测试环境死都测不出来,而生产环境却一堆的神秘消失的资料!

作死啊... ...

浪费我足足两天的时间,我都快要怀疑是微软的问题了... ...,不过我觉得微软不会犯这种超低级的错误啊.

最后我整个数据库服务器搜索了一下sysmail_mailitems这个关键字,发现居然有某个DB下某个SP在使用它.. ... 终于被我逮着了.

交接啊,交接啊,下次有人离职一定要强调让其交接好. 特别是这种连资料都没有的东西.

[鸭梨山大帝]

结贴,感谢各位的回复

[唐诗三百首]

原来如此,

[快溜]

sysmail_event_log 能看到什么

[唐诗三百首]

建議可以直接查詢mail server上的日誌,

應該有更詳細的信息, 無法派送也可能是mail server或網絡問題,

也可能是對方的郵件服務器把你方mail server設為黑名單之類的.

[鸭梨山大帝]

这个是 视图
sysmail_event_log
的源代码:

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO

CREATE VIEW sysmail_event_log
AS
SELECT log_id,
       CASE event_type 
          WHEN 0 THEN 'success' 
          WHEN 1 THEN 'information' 
          WHEN 2 THEN 'warning' 
          ELSE 'error' 
       END as event_type,
       log_date,
       description,
       process_id,
       sl.mailitem_id,
       account_id,
       sl.last_mod_date,
       sl.last_mod_user
FROM [dbo].[sysmail_log]  sl
WHERE (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) OR 
      (EXISTS ( SELECT mailitem_id FROM [dbo].[sysmail_allitems] ai WHERE sl.mailitem_id = ai.mailitem_id ))
GO

它其实是来源于sysmail_log的.其加了权限限制... 或者资料存在性检查.
sysmail_log看不到的,它也看不到.sysmail_log能看到的,它也不一定能看到...

[鸭梨山大帝]

"建議可以直接查詢mail server上的日誌"
sysmail_log这个就是mail server的日志.

"無法派送也可能是mail server或網絡問題,"
一些邮件发送失败,我确定是网络问题,造成邮件无法发送.
但是即使邮件无法发送,也不应该删除掉sysmail_mailitems表中的资料,这不符合逻辑.
既然log表中有,而又无法确定是否发送失败的,还删除?这样的话,如何追踪问题..?

[Quote=引用 3 楼 ap0405140 的回复:]

建議可以直接查詢mail server上的日誌,

應該有更詳細的信息, 無法派送也可能是mail server或網絡問題,

也可能是對方的郵件服務器把你方mail server設為黑名單之類的.
[/Quote]

[唐诗三百首]

既然用系统表有点混乱,建议自建一个邮件发送记录表,

每次发送database mail前插入一条记录(根据先写日志的原则),

发送成功后update发送状态为"已发送".