From 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)