Debugging database mail issues in your SQL Server with T-SQL

Problem. Your database mail suddenly stopped sending out email notifications on completed jobs and tasks. You try to send out an email test message from your SSMS and nothing is sent out.

Solution. One thing that could cause this issue is when your database mail suddenly hangs or stops in the production host server. There are number of ways that you can start to troubleshoot and investigate this issue.

First make sure your database mail is still enabled via sp_configure.

sp_configure
GO

Check if the user account of the application that needs to use the database mail is member of the DatabaseMailUserRole by running below statement,

EXEC msdb.sys.sp_helprolemember ‘DatabaseMailUserRole’;

Check also if the user account has access to at least one user database mail profile,

EXEC msdb.dbo.sysmail_help_principalprofile_sp;

Check and verify that your database mail is started and running;

EXEC msdb.dbo.sysmail_help_status_sp;

And of course you need to start it if it is not running then run statement,

EXEC msdb.dbo.sysmail_start_sp;

If the external program for database mail is running, you may check the mail queue by running,

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = ‘mail’;

You would want to find that your mail queue state should be ‘RECEIVES_OCCURING’. But if this is not the case you can stop the mail queue by running statement,

sysmail_stop_sp

and start it again by,

sysmail_start_sp

Also you would want to check if the suddenly stopping of your database mail happens for some user accounts only. You can then run below statement;

SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems;

and to view the error messages from the database mail log, run the statement

SELECT * FROM msdb.dbo.sysmail_event_log;

or you can view from SSMS Log File Viewer window and filter out logs from database mail. But note that errors such as invalid email addresses which prevent the mail from being delivered is not contained in the database mail log.

If you feel that you have the need to increase the successful mail delivery rate you can increase the number of times Database Mail attempts to send each message. Start the Database Mail Configuration Wizard, and select the View or change system parameters option. Alternatively, you can associate more accounts to the profile so upon failover from the primary account, Database Mail will use the failover account to send e-mails.

From my own experience, the cause of our database mail suddenly stopping during a job process was a very large email item that had to be sent out and the external program just hanged. I had to stop the external program for the database mail in the Task Manager in the host server and issue a KILL statement for the process id that is blocked by the database mail process. The case might be different with yours as the issue can also be with your SMTP server.

 

 

 

 

Advertisements

2 responses to “Debugging database mail issues in your SQL Server with T-SQL

  • Jingles

    When I had messages stuck in the Queue…

    I tried stopping with sysmail_stop_sp and then starting with sysmail_start_sp. Both commands completed successfully, however, the items still remained stuck in the queue.

    I then noticed in the Windows Task Manager, the DatabaseMail.exe process would not disappear when running sysmail_stop_sp. I then killed the DatabaseMail.exe process in Windows Task Manager, then ran sysmail_start_sp, and boom, messages started flowing again.

    I hope this helps as it took me about 5 hours to get here!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

SHE GOES GLOBAL

Travel the world, change your life.

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment