Azure DevOps Server 2020邮件通知作业挂起问题排查求助

问题概述 #

运行Azure DevOps Server 2020 Update 1.2，本月第二次出现邮件通知系统失效，但测试邮件功能正常（说明SMTP服务器配置无误）。重启后台作业代理可临时解决问题，队列中的邮件会在数小时内发送。现需：

• 数据库查询语句以查找相关错误信息
• 当前/下次问题发生时可提取的有用日志信息

已提供的事件日志 #

重启作业代理时的事件日志 #

Detailed Message: TF400804: The Azure DevOps Job Agent was terminating execution but the following jobs would not come to a stop in a timely manner: [JobId: b1516502-4633-432b-bdb3-74c802c5f2b7, 
Name: Team Foundation Server Send Mail Job, Extension: 
Microsoft.TeamFoundation.JobService.Extensions.Core.SendMailJobExtension]; [JobId: a96d6177-beef-477a-a2ee-2c31433214d0, 
Name: Notifications E-Mail Delivery, Extension: Microsoft.VisualStudio.Services.Notifications.Extensions.UserDeliveryJob]; [JobId: 8833fc71-42ca-441b-ab12-25314877772d, 
Name: Git User Delivery, Extension: Microsoft.TeamFoundation.JobService.Extensions.Core.UserDeliveryJobGit]; [JobId: 631f49b3-46e1-42ec-8fff-081bd176c18a, 
Name: WorkItem User Delivery, Extension: Microsoft.TeamFoundation.JobService.Extensions.Core.UserDeliveryJobWorkItems]; [JobId: 8833fc71-42ca-441b-ab12-25314877772d, 
Name: Git User Delivery, Extension: Microsoft.TeamFoundation.JobService.Extensions.Core.UserDeliveryJobGit]

每30分钟触发的事件日志 #

Detailed Message: TF400527: An error occurred while attempting to send an email (Request Id: 23eacfe9-3571-487e-ba2b-2faad799988b, Requested By: a10c324c-6462-4808-80f5-ebce59c90074). Further email sending errors that occur within the next 5 minutes might not be logged. Verify that the email notification settings are correct in the Azure DevOps Administration Console.

Exception Message: Failure sending mail. (type SmtpException)
Exception Stack Trace:    at Microsoft.TeamFoundation.Framework.Server.MailSenderExtensions.SendSynchronously(VssMailSender sender, IVssRequestContext requestContext, MailMessage message, String area, String layer, Stopwatch stopwatch)
   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationMailService.Send(IVssRequestContext requestContext, IEnumerable`1 messages)
   at Microsoft.TeamFoundation.JobService.Extensions.Core.SendMailJobExtension.SendMails(IVssRequestContext requestContext, IEnumerable`1 mailsToBeSent, ICollection`1 sentMails)

Inner Exception Details:

Exception Message: The operation has timed out. (type WebException)Exception Stack Trace:    at System.Net.Mail.SmtpConnection.ConnectAndHandshakeAsyncResult.End(IAsyncResult result)
   at System.Net.Mail.SmtpTransport.EndGetConnection(IAsyncResult result)
   at System.Net.Mail.SmtpClient.ConnectCallback(IAsyncResult result)

数据库查询语句 #

以下查询需在Azure DevOps的配置数据库（通常名为Tfs_Configuration）中执行：

1. 查询失败的邮件发送作业历史 #

SELECT 
    j.JobId,
    j.Name,
    j.ExtensionName,
    jh.QueueTime,
    jh.StartTime,
    jh.FinishTime,
    jh.Result,
    jh.ResultMessage
FROM 
    tbl_Job j
JOIN 
    tbl_JobHistory jh ON j.JobId = jh.JobId
WHERE 
    j.Name IN ('Team Foundation Server Send Mail Job', 'Notifications E-Mail Delivery')
    AND jh.Result = 'Failed'
ORDER BY 
    jh.FinishTime DESC;

2. 查询邮件发送相关的事件日志 #

SELECT 
    EventTime,
    Source,
    EventId,
    Message,
    Details
FROM 
    tbl_EventLog
WHERE 
    Message LIKE '%TF400527%' OR Message LIKE '%Send Mail%'
ORDER BY 
    EventTime DESC;

3. 查询待发送的邮件队列 #

SELECT 
    MailId,
    Recipients,
    Subject,
    QueueTime,
    AttemptCount,
    LastAttemptTime
FROM 
    tbl_MailQueue
WHERE 
    SentTime IS NULL
ORDER BY 
    QueueTime ASC;

额外日志收集建议 #

下次问题发生时，除现有日志外，还需收集以下信息：

• 作业代理详细日志：
  定位Azure DevOps Server安装目录下的作业代理日志，路径通常为C:\Program Files\Azure DevOps Server 2020\Application Tier\JobAgent\Logs，收集问题发生前后1小时内的JobAgent*.log文件。

• SMTP服务器日志：
  联系SMTP服务器管理员，获取问题发生期间的连接日志，检查是否有Azure DevOps服务器的连接超时、拒绝或异常断开记录。

• Azure DevOps应用层日志：
  收集应用层的详细日志，路径通常为C:\Program Files\Azure DevOps Server 2020\Application Tier\Web Services\Logs，重点关注TfsServices*.log中与邮件发送相关的条目。

• 系统性能监控数据：
  记录问题发生期间Azure DevOps服务器的CPU使用率、内存占用、网络带宽情况，排查是否因资源耗尽导致作业卡死。

• 作业队列实时状态：
  在问题发生时执行以下查询，查看作业队列的阻塞情况：

  SELECT 
      j.JobId,
      j.Name,
      jh.QueueTime,
      jh.StartTime,
      DATEDIFF(MINUTE, jh.StartTime, GETDATE()) AS RunningMinutes
  FROM 
      tbl_Job j
  JOIN 
      tbl_JobHistory jh ON j.JobId = jh.JobId
  WHERE 
      jh.FinishTime IS NULL
      AND j.Name IN ('Team Foundation Server Send Mail Job', 'Notifications E-Mail Delivery')
  ORDER BY 
      RunningMinutes DESC;
  

内容的提问来源于stack exchange，提问作者Nico