asked on

Unsent Emails In Database Mail Queue

I created a Database Mail profile and account and successfully sent an email using the Database Mail Configuration Wizard. I then created a Database Mail profile and account using sysmail_add_profile_sp, sysmail_add_account_sp, and sysmail_add_profileaccount_sp. All went well until I attempted to send an email using the new profile. SQL Server didn't throw any errors, but the email just got stuck in the queue. I now have a bunch of emails stuck in the queue. I've checked the following values:

1. Surface Area Configuration is enabled for DB Mail
2. As a member of sysadmin, my account is a member of msdb.DatabaseMailUserRole.
3. I confirmed that database mail is started.
4. The view sysmail_event_log isn't showing any errors.

It sure looks like the second profile/account pair I created is buggy. How do I purge the contents of the queue to start fresh?
sysmail-mailitems.xls

ASKER CERTIFIED SOLUTION

James Murrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jdana

ASKER

The Service Broker is enabled.
Database mail is running.
The emails still reside in the queue.
No error messages in the log.

If an email in the queue is invalid (bad profile, bad account, bad something...). Will subsequent email (that may be valid) get stuck?

James Murrell

unsure if other will queue: sorry

re: problem one...users must be a member of the DatabaseMailUserRole....are they?

jdana

ASKER

I'm in the local Windows Administrators group on my development machine, hence I'm in the sysadmin server role. As a double-check, I just ran

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';

My user account was listed.

J

James Murrell

mmmmm. only thing i can think off (last resort)

delete user with bad email and recreate:

please only do this as last resort - if no expert join in: after 12 hours click the request assistance link

ask if more expert could help

jdana

ASKER

Thanks for the suggestions. I figured out how to purge the queue (by running DatabaseMail90.exe manually). It now appears that the profiles and accounts were just fine. I ran the scripts on the production server and was able to send email without a hitch. So, it appears that the issue is associated with server configuration on my development machine.