Category Archives: SQL Server general

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

Aha!

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. 

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:
https://technet.microsoft.com/en-us/library/ms187540(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms190630(v=sql.105).aspx

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:
http://dbatasks.blogspot.com/2012/11/troubleshooting-database-mail-part-ii.html

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.

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!