Database Mail items stuck in unsent status

Question

Database Mail items stuck in unsent status

webrunner

SSC-Dedicated

Points: 31718
More actions
June 10, 2021 at 9:47 pm

#3894661
Hello experts,
I'm trying to send a test email via Database Mail and am nor receiving it. I pinged the mail server and do get responses. I also made sure that the Agent XPs are on and that SQL Browser is running.
These are the row counts for the queries I tried.
```
SELECT * FROM msdb.dbo.sysmail_allitems;-- 6 items
SELECT * FROM msdb.dbo.sysmail_faileditems;-- 0 items
SELECT * FROM msdb.dbo.sysmail_unsentitems;-- 6 items status = 'unsent'
SELECT * FROM msdb.dbo.sysmail_event_log;-- 1 row (this row shows when I started the mail queue via t-sql)
```
Does anyone know how I can further troubleshoot this issue? Thanks for any help.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

May need to start the dbmail process sometimes it’s a bit funny and will refuse to auto start when mail items hit the queue.

sp_start_dbmail

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 2

Also, Microsoft has some documentation on what to try:

https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-mail-queued-not-delivered?view=sql-server-ver15

The important tip they offer is:

If the last attempt did not start the external program, verify that the Database Mail External Program is located in the correct directory, and that the service account for SQL Server has permission to run the executable.

I would also check the logs. Not just the mail logs, but all the database logs and the windows logs. May have some clues in there. Also, might not hurt to check for any blocking.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

webrunner SSC-Dedicated Points: 31718 More actions · Answer 3

Thanks for your responses.

I think I found the issue by running through the info at the page below. It looks like I need to get .NET Framework 3.5 added to the host.

Troubleshooting Database Mail for SQL Server

https://houseofbrick.com/troubleshooting-database-mail-for-sql-server/

I'll report back if that resolution works.

Thanks again!

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31718 More actions · Answer 4

Just to follow up - the .NET 3.5 Framework did the trick. The mail queue is now sending emails.

Thanks again for your feedback!

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html