SQL Server 2016 – Database mail queued but not sending

Have you installed SQL Server 2016, enabled Database Mail and not been able to send a test email?  Does the email queue show a list of queued mail items but no entries in the Database Mail log?  This may help.

I installed SQL Server 2016 with CU1, used a domain user as the owner of the SQL Server accounts and gave that domain user local administrator privileges. I turned on database mail and created a profile and email account to send mail. When I attempted to send a test email, no test email ever arrived. Hmm, time to start troubleshooting.

First, I checked the Database Mail log. This was accomplished by going to the Object Explorer window, expanding ‘Management’ and right clicking on ‘Database Mail’ and choosing ‘View Database Mail Log’:


How to view SQL Server Database Mail log

No entries. Hmmm.

Next was to see what MSDB thought about the process. Querying MSDB using the built-in mail stored procedures and system tables yielded little results:

-- This one told me that Database Mail was started
EXEC msdb.dbo.sysmail_help_status_sp;
-- Here I learned that there were 5 items queued and the last times I tried sending mail
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
-- This confirmed none of the email was sent
SELECT * FROM msdb.dbo.sysmail_sentitems;
SELECT * FROM msdb.dbo.sysmail_unsentitems;
-- Is Service Broker enabled? It has to be to send mail
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- I tried stopping and restarting the Database Mail exe
EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_start_sp;

The domain account responsible for running the SQL Server service and agent was a local administrator. I made it an instance administrator and restarted the instance. Still no success. And since I (and the service accounts) were both in the ‘sysadmin’ server role, permissions and ownership in the MSDB database role ‘DatabaseMailUserRole’ were not the issue.

My last attempt was to check the Database Mail executable directly. A Windows search for ‘databasemail’ showed it to be in “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn”. Double clicking (or right click and ‘Run As Administator’) the executable showed me that .Net 3.5 was not installed:


.Net Framework 3.5 is not installed


This was a Windows Server 2012 R2 box, so I had to add .Net 3.5 framework.  Which meant I had to dig up the Windows install media (there may be ways to do it without but I didn’t dig that deep in Bingle).

Opening Control Panel and choosing ‘Turn Windows features on or off’ brought up the wizard. control_panel_windows_features

Choose ‘Role-based or feature-based installation’ > ‘Next’.  Select the server and click ‘Next’.  Leave all boxes blank on the ‘Select server roles’ screen and choose ‘Next’. On the ‘Select features’ screen, check the box for ‘.Net Framework 3.5 features’ and click ‘Next’.

The ‘Confirm installation selections’ screen will have a warning at the top beginning with ‘Do you need to specify an alternate source path?…’.  Below the big white box in the middle of the windows, click the link ‘Specify an alternate source path’; this brings up a new window.  Put the path to the Windows media.  For me, I’d mounted the disc to E:\, my source path was ‘E:\sources\sxs’.


Supply alternate source path

Click ‘Install’ (the above came from https://technet.microsoft.com/en-us/library/dn482071.aspx, which claims not to be maintained).

Once .Net 3.5 was installed, I went back and double clicked the ‘databasemail.exe’ from above.  All my mail that was queued was delivered.  And all test emails were now sent successfully.

These help with troubleshooting:

This one has a comment at the end that helped point me in the right direction and says that CU2 may resolve the above issue:

SQL Saturday #540 in Sacramento CA – silky smooth…

Another year in the books, another amazingly successful event!  SQL Saturday #540 in Sacramento was our 5th endeavor and was arguably our best effort.  No law enforcement involvement, no fires or natural disasters and no one leaving dissatisfied.sqlsat540_web

This year boasted an international speaker lineup; it was great to meet Cathrine Wilhelmsen (B|T) from Norway.  We had a good showing of first timer speakers to Sacramento, thanks to Simon Cho (B), Anthony Nocentino (B|T), Reeves Smith (B|T), Bill Fellows (B|T), Elizabeth Bock (B|T), Meagan Longoria (B|T), Sayed Saeed (B|T) and Tim Plas (B|T).  Sacramento was well represented; shout out to Chris Anderson (B|T) for being a first time SQL Saturday speaker and contributor to the event.  Dilip Nayak (T) and I did a tag team session on NoSQL and MongoDB that was well received, although buzzword bingo did not seem to go over as well this time.  Pro tip: make sure to not get a session right after lunch, it will limit the amount of nodding heads in the audience.

Over 150 attendees were recorded, making this our second highest attendance level.  While the number of sponsors was lower this year, the support that they gave enabled the event to be great success.  Thanks to Tintri, DELL software, Microsoft, DesignMind, Plus Consulting, Idera, PASS, Devart, Robert Half and Sutter Health.  With the temperature outside going into the triple digits (I think the high that day was 103 F), the feeling inside was a constant “78 and sunny”.  Speaking with the team, we all agreed that this may have been our smoothest run event to date.

Thanks to all the speakers, sponsors and attendees.  Please visit the Sacramento SQL Server user group LinkedIn page to give us any feedback.


See you next year!


SQL Saturday #312 – Sacramento, CA

SQL Saturday #312 will be held July 12, 2014 in Sacramento CA, our third annual event.  I’ve chosen to join my Sacramento SQL Server user group board members and participate in a “lightning talk”, outlining a how to grant permissions to users through the signing of stored procedures.  It’s a great topic and can be put into use very easily and quickly, with a good bang for your buck.

Mitch Bottel (B|T), Dilip Nayak (T) and Joe Simmons (T) will be joining me.  Check out the abstract at http://sqlsaturday.com/viewsession.aspx?sat=312&sessionid=22630.

Hope to see you there!

Find SQL Server services running on a remote server using Powershell

You’re given a list of Windows servers and told they host multiple SQL Server instances.  How do you find which services are running?  Use Powershell:


get-service mssql* -computername myServer


It will give a nice list of services that have MSSQL at the beginning of their names:

Status Name DisplayName
Stopped MSSQLServerADHe… SQL Server Active Directory Helper
Stopped MSSQLServerADHe… SQL Active Directory Helper Service

A shorter version:

gsv mssql* -cn myServer

Running .sql files from within the Query Editor using SQLCMD mode

As a DBA, I have a plethora of queries that I run on a consistent basis, commands that give me information about who’s connected to the instance, how mirroring is performing, what types of permissions a user has, etc.  Early in my career I began cataloging these queries.  My first file was called ‘useful_sql.txt’ (I still have it to this day) and it grew longer with each learned action.  I’d open up SSMS, click the folder icon to open the file and cut/paste any commands I needed to a new editor window.  What a pain.  As I grew in the profession, I began creating individual .sql files with a meaningful title for each specific query, but I still had to click the folder, find the file, wait for it to open in SSMS then click to execute the query.  A little less painful, but still not ideal.

Powershell offered some options but meant I had to leave the query editor.

Here was my solution: SQLCMD mode.

Enabling SQLCMD mode in the query editor allows you to run commands just as you would from the SQLCMD prompt.  The command I find most useful is:

:r <filename>

The file is read and the statements executed.  Now, instead of the hassle of opening a file and executing the commands thru a multi-click process, a query can be executed without leaving the query editor and keyboard, e.g.

:r I:/scripts/showSQL.sql

This executes the following query stored in the ‘showSQL.sql’ file that shows me who’s running which statement:

   CONVERT(VARCHAR(8000),(SELECT SUBSTRING(sqltext.text,req.statement_start_offset/2,
   WHEN req.statement_end_offset = -1 THEN 8000
   ELSE (req.statement_end_offset-req.statement_start_offset)/2
   END))) as 'SQL statement',
   DB_NAME(req.database_id) as "db_name",
   sess.login_name as "user name",
   sys.dm_exec_requests req
   sys.dm_exec_sql_text(sql_handle) AS sqltext
   sys.dm_exec_sessions sess
   on req.session_id = sess.session_id
   req.session_id > 50
order by


For the above example, I have a file share that I’ve mapped to the I: drive.  I place all my scripts on that drive in the /scripts directory.  Files will be read relative to the directory that SQLCMD is invoked from; from the query editor this usually means c:\windows\systems32.  While you could place all your .sql scripts there, it’s not a good idea.  I suggest a fully qualified path to a place of your choosing.

SQLCMD mode has to be enabled in the SSMS query editor. It can be done per query editor session or set to open the editor in SQLCMD mode by default:

  • To enable SQLCMD mode for the current query editor session, go to the Query menu and select SQLCMD mode.
  • To enable SQLCMD mode by default, go to Tools > Options. In the left hand pane single click Query Execution then click the textbox By default, open new queries in SQLCMD mode


There a few other uses for SQLCMD mode as well as some limitations, here’s the link to BOL if you’d like more information:


Happy scripting!

<Disclaimer: the sample script was not created by me but I do not remember from where I copied it.  If it’s your work, please post a comment and I’ll be happy to give you credit.>

My first SQL Server presentation – database mirroring w/ Transparent Data Encryption

I’ve finally cut my teeth on a technical, public presentation on SQL Server.

This wasn’t explaining to a bunch of co-workers and managers about the latest features in SQL Server 2012.  It wasn’t sharing my understanding of whatever piece of Scripture we were studying in church.  It wasn’t addressing a group of parents about our team’s achievements over the last season.  This was a group of my peers who potentially knew as much or more about the subject matter and, being in IT, we’re probably not afraid to let me know when I was wrong.

My first mistake: the topic for the presentation was way too large, Database mirroring with Transparent Data Encryption.  I chose the subject because I was familiar with the technical aspects and figured that I could spin it as ‘High Availability and passing an audit at the same time’.  I had already done it in my environments so felt comfortable presenting…my relaxed demeanor was one area I received compliments.

The day of the presentation I discovered that the computer I had brought from home had suffered a failure. I was using it, a cross-over cable and my laptop to simulate server to server communication (for the mirroring).  I scrambled to get mirroring working between two instances on my laptop to no avail (some of you saw my #SQLHELP tweet).  The site where the presentation was being held did not offer WiFi, but I had a cellular dongal (borrowed from wife) and could remote into my test servers at work.  This was a last resort; it also meant a live demo.

I arrived early and tested my cell connection: 100% strong with full bars.  Sweet, one potential speed bump behind me.  We went through our normal meeting announcements and then it was my turn…I now had the Conch.  A quick prayer to the DemoGods and I was off and running.

I had way too many windows open.  My font size was too small.  I switched context between servers often. The code was not fully commented.  The last piece of the demonstration failed.


I was relaxed throughout.  I was knowledgeable about the subject.  I kept consistent eye contact with my audience.  I engaged the audience regularly.

For what I had to overcome, it turned out fairly well.  The feedback I received was constructive and positive which is a testament to our Sacramento user group.  It gave me the confidence to want to do another.

I’m looking forward to my next opportunity…this time I’ll choose a topic that can be explained in 100 words or less.

Check out http://sac.sqlpass.org for my code and slide deck.

“In transit” encryption to SQL Server

To encrypt connections to the SQL Server engine, there are a few options:

  1. IPsec, that encrypts the whole network packet
  2. SSL (Secure Sockets Layer) using “Force Encryption”, which only encrypts the packets sent to the database
  3. IPsec + SSL

“In transit” encryption is the encryption of the data packets at the network level. IPsec does this well, but is set through the operating system and encrypts ALL packets that come and go to the server. If the network stream needed to be analyzed to troubleshoot a problem, IPsec would have to be turned off. This might involve the network analyst, the server admin or a combination of the two. If the DBA does not wear one or both of those hats, it’s extra resources that may have to be tapped.

Using SSL with SQL Server is a relatively easy task. It has the advantages of only encrypting the traffic being sent to the SQL Server engine and can be controlled (for the most part) solely by the DBA.

I won’t go into the implementation of IPsec, but will offer this site.

Encryption using SSL has a few choices: use a certificate from a trusted root authority, create your own certificate or use the self-signed certificate that gets created when SQL Server starts.

A certificate from a trusted root authority needs to be purchased from a vendor such as GoDaddy (Google for more vendors).

You can create your own certificate using the IIS Resource Kit. Here’s a link that has a decent explanation; the Microsoft documentation will have more information.

For the above two options, the certificate will need to be imported into the SQL Server Windows server and into any clients wanting to connect to the database engine.

To install a certificate (from SQL Server Books Online):

  1. On the Start menu, click Run, and in the Open box, type MMC and click OK.
  2. In the MMC console, on the File menu, click Add/Remove Snap-in.
  3. In the Add/Remove Snap-in dialog box, click Add.
  4. In the Add Standalone Snap-in dialog box, click Certificates, click Add.
  5. In the Certificates snap-in dialog box, click Computer account, and then click Finish.
  6. In the Add Standalone Snap-in dialog box, click Close.
  7. In the Add/Remove Snap-in dialog box, click OK.
  8. In the Certificates snap-in, expand Certificates, expand Personal, and then right-click Certificates, point to All Tasks, and then click Import.
  9. Complete the Certificate Import Wizard, to add a certificate to the computer, and close the MMC console. For more information about adding a certificate to a computer, see your Windows documentation.

To enable SSL in SQL Server using the imported certificate, open the ‘SQL Server Configuration Manager’. In the left hand pane, right click ‘Protocols for MSSQLSERVER’ and choose ‘Properties’. (If you’re connecting to a named instance, it will be ‘Protocols for “named instance“‘):

SQL Server Configuration Manager

Choose the ‘Certificates’ tab, then choose your imported certificate from the drop down menu.

Choose the ‘Flags’ tab and set ‘Force Encryption’ to ‘Yes’. SSL will now be implemented once the SQL Server service is restarted.

To enable SSL using the self-signed certificate that SQL Server creates on each service start, simply set ‘Force Encryption’ to ‘Yes’ and restart the service. No certificate needs to be installed (see above).


The self generated and self-signed certificates offer the weakest security. The self-signed certificate encryption method does not protect against “Man in the Middle” attacks.

“In transit” encryption could have a performance impact, as there is additional overhead in the encryption and decryption of the packet.

Clients not using the SQL Server connection libraries may not be able to communicate with the engine when encryption is turned on. Tests can be done using the self-signed certificate.

Regardless if “Force Encryption” has been enabled, SQL Server always encrypts the authentication process when a SQL Server login connects to the engine.