SQL Server Agent and SQL Database Mail issues

• equipe9

  Old Hand

  Points: 361

  More actions

  January 8, 2018 at 6:36 pm

  #330793

  I am trying to get database mail setup on SQL Server 2014.

  I have

  1. Setup Database Mail through the wizard
  2. Setup an Operator

  I can successfully send an email when I right click on Database Mail and select Send Test E-mail.

  However, when I schedule a job and ask it to send an email on completion or success, the e-mail does not go through.

  Other interesting behavior is that under the SQL Server Agent the option to start, stop or restart is greyed out, so I have to go to services or to the SQL Configuration Manager to restart the SQL Server Agent, which I have done after I setup database mail.
  
  Also, 

  Under the SQL Server Agent, when I go to properties and then the Alert System tab, I try to check the enable mail profile tab, but when I do so and click ok it accepts it, but when I go back in later it doesn't maintain this check mark.
  
  Very weird behavior, I've tried everything and I'm stumped at this point, so any advice would be greatly appreciated.

  Thanks so much in advance.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 9, 2018 at 8:24 am

  #1975085

  Mike Saedi - Monday, January 8, 2018 6:36 PM

  I am trying to get database mail setup on SQL Server 2014.

  I have

  1. Setup Database Mail through the wizard
  2. Setup an Operator

  I can successfully send an email when I right click on Database Mail and select Send Test E-mail.

  However, when I schedule a job and ask it to send an email on completion or success, the e-mail does not go through.

  Other interesting behavior is that under the SQL Server Agent the option to start, stop or restart is greyed out, so I have to go to services or to the SQL Configuration Manager to restart the SQL Server Agent, which I have done after I setup database mail.
  
  Also, 

  Under the SQL Server Agent, when I go to properties and then the Alert System tab, I try to check the enable mail profile tab, but when I do so and click ok it accepts it, but when I go back in later it doesn't maintain this check mark.
  
  Very weird behavior, I've tried everything and I'm stumped at this point, so any advice would be greatly appreciated.

  Thanks so much in advance.

  Did you check for errors in the logs?
  That setting executes an extended stored procedure to update the registry. When you make the changes, before hitting okay, script out the changes.
  It should generate something like:
  USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'MailProfileName'
GO

  Try executing that manually and see if you get an error.
  You could also try going into regedit and manually updating that key and see if you get an error.

  The issue with the start, stop pause being disabled could be a few different things and may or may not be related to enabling the mail profile for agent.
  First check would be to run SSMS as an administrator by right clicking on SSMS and selecting Run as Administrator. It's not the same as being in the administrators group.

  Sue

• equipe9

  Old Hand

  Points: 361

  More actions

  January 9, 2018 at 8:34 am

  #1975087

  Sue_H - Tuesday, January 9, 2018 8:24 AM

  Mike Saedi - Monday, January 8, 2018 6:36 PM

  I am trying to get database mail setup on SQL Server 2014.

  I have

  1. Setup Database Mail through the wizard
  2. Setup an Operator

  I can successfully send an email when I right click on Database Mail and select Send Test E-mail.

  However, when I schedule a job and ask it to send an email on completion or success, the e-mail does not go through.

  Other interesting behavior is that under the SQL Server Agent the option to start, stop or restart is greyed out, so I have to go to services or to the SQL Configuration Manager to restart the SQL Server Agent, which I have done after I setup database mail.
  
  Also, 

  Under the SQL Server Agent, when I go to properties and then the Alert System tab, I try to check the enable mail profile tab, but when I do so and click ok it accepts it, but when I go back in later it doesn't maintain this check mark.
  
  Very weird behavior, I've tried everything and I'm stumped at this point, so any advice would be greatly appreciated.

  Thanks so much in advance.

  Did you check for errors in the logs?
  That setting executes an extended stored procedure to update the registry. When you make the changes, before hitting okay, script out the changes.
  It should generate something like:
  USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'MailProfileName'
GO

  Try executing that manually and see if you get an error.
  You could also try going into regedit and manually updating that key and see if you get an error.

  The issue with the start, stop pause being disabled could be a few different things and may or may not be related to enabling the mail profile for agent.
  First check would be to run SSMS as an administrator by right clicking on SSMS and selecting Run as Administrator. It's not the same as being in the administrators group.

  Sue

  Hi Sue,

  Thanks for your reply.

  I started SQL Server running as Administrator and I can start and stop the agent from right-clicking on the agent from within SSMS now.

  The issue with the enabling the mail profile, however, is still there.

  I scripted out the changes under the SQL Server Agent Properties and ran the code manually 

  USE [msdb]
  GO
  EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,
          @databasemail_profile=N''
  GO

  It completed successfully according to SSMS, but when I close it and go back in, it does not keep the change.

  Do you have any other suggestions?

  Thanks,

  Mike

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 9, 2018 at 8:39 am

  #1975088

  Mike Saedi - Tuesday, January 9, 2018 8:34 AM

  Hi Sue,

  Thanks for your reply.

  I started SQL Server running as Administrator and I can start and stop the agent from right-clicking on the agent from within SSMS now.

  The issue with the enabling the mail profile, however, is still there.

  I scripted out the changes under the SQL Server Agent Properties and ran the code manually 

  USE [msdb]
  GO
  EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,
          @databasemail_profile=N''
  GO

  It completed successfully according to SSMS, but when I close it and go back in, it does not keep the change.

  Do you have any other suggestions?

  Thanks,

  Mike

  You haven't specified a database mail profile. Whatever the name of the profile is that you need to use for Agent, you would add it to @databasemail_profile. Something like:
  @databasemail_profile=N'NameOfYourMailProfile'

  Sue

• equipe9

  Old Hand

  Points: 361

  More actions

  January 17, 2018 at 1:23 pm

  #1976193

  Hi Sue,
  I was able to enable the database profile and it shows up under the SQL Server Agent Properties as checked now.  The test button however is still grayed out.
  Other thing is when I try to go to the history tab and change the default values to Remove Agent History Older than 2 months and then click OK, the change is accepted, but when I go back in to the same tab it's then returned to the default values.
  
  It acts like it accepts the change, but doesn't want to retain it. I can script out the change also and run it manually, but again when I go back into the agent, it is not retained.
  Any other ideas?

  Thanks again for your help.
  Mike

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 17, 2018 at 1:39 pm

  #1976197

  Mike Saedi - Wednesday, January 17, 2018 1:23 PM

  Hi Sue,
  I was able to enable the database profile and it shows up under the SQL Server Agent Properties as checked now.  The test button however is still grayed out.
  Other thing is when I try to go to the history tab and change the default values to Remove Agent History Older than 2 months and then click OK, the change is accepted, but when I go back in to the same tab it's then returned to the default values.
  
  It acts like it accepts the change, but doesn't want to retain it. I can script out the change also and run it manually, but again when I go back into the agent, it is not retained.
  Any other ideas?

  Thanks again for your help.
  Mike

  The test button is greyed out with Database Mail. I think it's there from the old SQL Mail which no one should use and those who had to years ago should be given pity.
  The history setting has been a bug for well over 10 years. There has been a connect item for it going back to 2009 when it was already a known issue. Here is the link:
  KJTAP - SAP: SQL SERVER AGENT 'REMOVE AGENT HISTORY' CANNOT BE ACTIVATED

  Use the limit the job history settings for maximum log size and maximum history for each job.

  Sue

• equipe9

  Old Hand

  Points: 361

  More actions

  January 17, 2018 at 1:45 pm

  #1976198

  Hi Sue,

  That is good to know about the SQL Server Agent History, but do you have any additional suggestions as to why e-mails are not going through?
  Test e-mails say queued, but then never come through.
  Not sure what else to try to get the e-mail notifications to come through.

  Thanks,
  Mike

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 17, 2018 at 2:55 pm

  #1976229

  Mike Saedi - Wednesday, January 17, 2018 1:45 PM

  Hi Sue,

  That is good to know about the SQL Server Agent History, but do you have any additional suggestions as to why e-mails are not going through?
  Test e-mails say queued, but then never come through.
  Not sure what else to try to get the e-mail notifications to come through.

  Thanks,
  Mike

  Hey Mike -
  Now you get to be familiar with the view and tables for database mail in msdb 🙂
  You can see if you get enough info from the log in SSMS - I usually use the views and tables instead of or in addition to the log you can view directly in SSMS. To view that log, go to the Database Mail under Management, right click and view Database Mail log. It's available when viewing any of the logs actually - look on the left when viewing a log and you can see the different logs you can select. That database mail log is just select from the view sysmail_event_log. Another view in msdb that can sometimes make it faster is to query sysmail_faileditems and check the description column.
  You may also want to check the sysmail_mailitems system table in msdb. It gives you some more information in terms of account used, profile used, requested by, sent by, etc.
  Most (maybe all) the views and tables are in msdb in the dbo schema and start with sysmail.

  Sue

• equipe9

  Old Hand

  Points: 361

  More actions

  January 17, 2018 at 3:31 pm

  #1976241

  Hi Sue,

  Here are my findings.

  Right clicking on view logs under database mail pulls back 0 records.

  When I run
  select * from [msdb].[dbo].[sysmail_event_log]
  or 
  select * from [msdb].[dbo].[sysmail_sentitems] 
  I get 0 records returned.

  When I run
  select * from [msdb].[dbo].[sysmail_faileditems] I get records back and the only thing that jumps out on me is that the values in the sent_status column all are failed
  select * from [msdb].[dbo].[sysmail_faileditems] I get records back but the only thing meaningful out of this query is that the values in the sent_status column all are unsent.

  Are there other things I should be looking for?
  Not sure where things are getting stuck.
  Any additional direction you can provide would be greatly appreciated.

  Thanks again,

  Mike

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 17, 2018 at 3:58 pm

  #1976244

  Mike Saedi - Wednesday, January 17, 2018 3:31 PM

  Hi Sue,

  Here are my findings.

  Right clicking on view logs under database mail pulls back 0 records.

  When I run
  select * from [msdb].[dbo].[sysmail_event_log]
  or 
  select * from [msdb].[dbo].[sysmail_sentitems] 
  I get 0 records returned.

  When I run
  select * from [msdb].[dbo].[sysmail_faileditems] I get records back and the only thing that jumps out on me is that the values in the sent_status column all are failed
  select * from [msdb].[dbo].[sysmail_faileditems] I get records back but the only thing meaningful out of this query is that the values in the sent_status column all are unsent.

  Are there other things I should be looking for?
  Not sure where things are getting stuck.
  Any additional direction you can provide would be greatly appreciated.

  Thanks again,

  Mike

  The database mail log wouldn't have no records. It would at a minimum have the startup and shutdown of the database mail process. The only time that I know of when you can't see log records through the GUI is if you are not a sysadmin. The failed records in the view you saw would be in the mail log in SSMS as well as the view. Do you know what roles you are in or what permissions you have...are you a sysadmin on that server?

  Sue

• equipe9

  Old Hand

  Points: 361

  More actions

  January 17, 2018 at 4:12 pm

  #1976245

  I was sysadmin on the SQL Server and changed myself to sysadmin on the Server itself also.
  I retried again after I changed myself to sysadmin on the server itself, logged out and logged back in, but still the same

  Do you have any other thoughts?

  Thanks,

  Mike

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 17, 2018 at 4:30 pm

  #1976246

  A few other things to try -
  Is broker enabled - execute:
  SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
  Check the status of database mail - execute:
  EXECUTE dbo.sysmail_help_status_sp ;

  Do you get any more rows if you query sysmail_allitems? Send a test email to yourself first and then check.

  Sue

• equipe9

  Old Hand

  Points: 361

  More actions

  January 17, 2018 at 5:34 pm

  #1976258

  Hi Sue,
  First query returns a value of 1, for is_broker_enabled.
  Second query returns a value of STARTED for status.
  Test e-mail returns additional records to the sysmail_allitems view but all records show up as "unsent" under the sent_status column as before.

  Do you have any other thoughts?

  Thanks again,
  Mike

• RandomStream

  Hall of Fame

  Points: 3735

  More actions

  January 17, 2018 at 5:42 pm

  #1976260

  At this point, is test email still working? If you are using Exchange, you may want to enlist Exchange admin to help you check on the server end.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 17, 2018 at 6:14 pm

  #1976261

  Mike Saedi - Wednesday, January 17, 2018 5:34 PM

  Hi Sue,
  First query returns a value of 1, for is_broker_enabled.
  Second query returns a value of STARTED for status.
  Test e-mail returns additional records to the sysmail_allitems view but all records show up as "unsent" under the sent_status column as before.

  Do you have any other thoughts?

  Thanks again,
  Mike

  Mike -

  When you were able to send an email initially, did it really send or was that in the unsent as well? Did you just get a message of it being queued for processing?
  If that's the case and you have never actually received any of the test emails then mail isn't setup right. Make sure the account for that profile being used is set correctly, whoever manages the mail/smtp is allowing relays for that server, need to use the correct ports and have the traffic allowed on the firewall for those ports, the required authentication, etc.
  I'd try another query of dbo.sysmail_event_log in msdb and make sure you are running it against the correct server. Not getting errors and nothing being logged doesn't make sense.
  Otherwise I'm running out of ideas. Check the queues is all I can think of. Check if the mail queue was activated recently:
  EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'

  And then check the queues in msdb to see if anything is backed up:
  SELECT * FROM dbo.ExternalMailQueue
SELECT * from sys.transmission_queue

  Sue