SQL Server 2014 DB Mail停止发送邮件问题求助

Got it, let’s troubleshoot your SQL Server 2014 DB Mail issue where messages are stuck as UNSENT in msdb.dbo.sysmail_allitems. Here are the practical steps I’d walk through to fix this:

• Verify SQL Server Agent is running
  DB Mail depends entirely on SQL Server Agent to process the mail queue. Open Services (services.msc) on your server, locate "SQL Server Agent (your instance name)", and confirm it’s up and running. If it’s stopped, start it immediately and set its startup type to Automatic to avoid this happening again.

• Test your DB Mail profile and account
  Fire up SQL Server Management Studio (SSMS), expand Management > Database Mail, right-click your active profile, and select "Send Test E-Mail". If the test fails, the error message will directly point to the root cause—think invalid SMTP credentials, blocked ports, or an unreachable mail server.

• Dig into the Database Mail Log
  In SSMS, right-click Database Mail and select "View Database Mail Log". Look for entries timestamped around when your messages stopped sending. Common red flags here include:

  • SMTP server connection timeouts
  • Failed authentication (wrong username/password)
  • Message size exceeding the SMTP server’s limit
  • Malformed recipient email addresses

• Manually retry stuck messages
  If the queue is hung up, you can kickstart a retry with this T-SQL command in the msdb database:

  -- Resend all UNSENT messages
  EXEC msdb.dbo.sysmail_resend_sp @item_id = NULL;
  
  -- Or resend a specific message (replace with your item_id)
  -- EXEC msdb.dbo.sysmail_resend_sp @item_id = '12345';
  

  Grab the item_id from msdb.dbo.sysmail_allitems where sent_status = 'UNSENT'.

• Check if Database Mail XPs are enabled
  Sometimes, advanced settings get tweaked accidentally. Run this query to confirm Database Mail extended procedures are enabled:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'Database Mail XPs';
  

  If the run_value is 0, enable them with:

  EXEC sp_configure 'Database Mail XPs', 1;
  RECONFIGURE;
  

• Test SMTP server connectivity
  Confirm your SQL server can reach the SMTP server using PowerShell or Telnet. For example:

  Test-NetConnection your-smtp-server.com -Port 587
  

  If this test fails, loop in your network team to check firewalls, routing rules, or whether the SMTP server is online.

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