Database MailFrom time to time I field questions regarding Database Mail usage within SQL. Questions come from all over discussing how to identify what the job is doing or what the job has done. Sure, you can send some test mails through the nice GUI part, but that is not what this post is about. I enjoy T-SQL and looking inside SQL the old fashion way so to speak so I utilize some simple queries that a colleague of mine recommended for me.

Database Mail in and of itself is a useful tool; it allows for notifications of failed SQL jobs for instance. The messages in and of itself can contain a plethora of information that can assist one in troubleshooting a variety of issues. According to Microsoft they state Database Mail in this manner – “Database Mail is designed for reliability, scalability, security, and supportability.”

**NOTE** Database Mail is not active by default; it has to be configured and turned on. The below information assumes that Database Mail is already set up. For information on how to set up Database Mail you can go here

To give a brief overview the below script is broken out into 7 mini scripts; these scripts consist of checks against Database Mail along with the process of stopping and restarting database mail. Please note the disclaimer and hope this helps with some of the questions that I’ve received thus far regarding Database Mail.

/**************************************************************************************************************

Disclaimer: Do not execute code found on the internet without testing on your local or testing environment. Running any code in a production environment that you find on the internet is not an acceptable practice and this site is not responsible for any repercussions that may follow if you choose to do so.

Scripts below are numbered; the corresponding numbers will give you a description of what they are utilized for.

1. The status of the Database Mail. Possible values are Started and Stopped (msdn article on sysmail_help_status_sp)

2. Stops the database mail queue that holds outgoing message requests (msdn article on sysmail_stop_sp)

3. Starts the database mail queue that holds outgoing message requests (msdn article on sysmail_start_sp)

4. Shows all the mail items

5. Shows all the unsent mail items

6. Shows all the sent mail items

7. Shows all the failed mail items

**************************************************************************************************************/

USE msdb

GO

/*1.*/ EXECUTE sysmail_help_status_sp

/*2.*/ EXECUTE sysmail_stop_sp

/*3.*/ EXECUTE sysmail_start_sp

/*4.*/ SELECT * FROM dbo.sysmail_mailitems (NOLOCK)

/*5.*/ SELECT * FROM dbo.sysmail_unsentitems (NOLOCK)

/*6.*/ SELECT * FROM dbo.sysmail_sentitems (NOLOCK)

/*7.*/ SELECT * FROM dbo.sysmail_faileditems (NOLOCK)

Advertisements

4 thoughts on “How’s Your Database Mail?

  1. Great article for walking though SQL Mail troubleshooting. I am running several SQL 2012 boxes with database mail setup on each to notify us on various sql agent job completions. Everything works as expected but occasionally a server does not send an email for no apparent reason. There are no errors, unsent messages or failed items anywhere that I can see. If we rerun the agent job sometimes the mail works. Other times we have to restart the SQL agent and/or server services in order for it to work. It almost feels like a bug but I can’t find anything on the internet. Any thoughts on this? Thank you!

    1. Thanks for the kind words Tom. To your problem when you say “There are no errors, unsent messages, or failed items anywhere that I can see”; does this also mean nothing appeared in the database mail log. I have seen on occasions with 2012 that on a fail over the database mail has to be restarted by running the stop and restart commands; I have not had one sporadically not send anything on failures. Let me know on the actual database log portion and I will see what I can drum up with known bugs etc.

      1. Chris, typicaly we see nothing in the database mail log when the email step in our agent job fails (last step) although we have seen a database mail start/stop event on occasion. A normal night shows the database mail process start (and stop about 20 minutes later) when it’s called. And email is sent as expected. The error we see in the agent job history after a fail is: Executed as user: DMS\ntservice. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed. We change nothing and it works for the next x days/weeks/months. Very strange.

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