Monday, November 20, 2017

Database Mail is stopped. Use sysmail_start_sp to start Database Mail.

I built a  new job recently for one of my customers.  Just a quick archive job which moves data from A to B, and sends a notification to an application Team after it has completed. ... or I should say it is supposed to send a notification via email.  The first run of that job failed with this message:

Message
Executed as user: DOMAINNAME\sqlservice. Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. [SQLSTATE 42000] (Error 14641).  The step failed.

Database Mail is stopped?  Sure isn't supposed to be.  I did a bit of research, and it seems this is not entirely uncommon.  As a workaround, I decided to check first to see if Database Mail was running, and then to start it, if needed.  Very simple, and it will prevent any failures sending notifications from the job, going forward.


DECLARE @MailStatus TABLE (CurrentState VARCHAR(10))
INSERT @MailStatus
EXEC msdb.dbo.sysmail_help_status_sp;

IF NOT EXISTS(
      SELECT 1 FROM @MailStatus
      WHERE CurrentState = 'STARTED'
      )
      BEGIN
            EXECUTE msdb..sysmail_start_sp; -- START IT, IF NEEDED

            EXEC msdb..sp_send_dbmail
              @profile_name = 'SQL_MailProfile',
              @recipients = somebody@somewhere.com',
              @subject='SERVERNAME  Archive Job ',
              @body='The daily Archive Job has completed successfully.'
      END
      ELSE 
      BEGIN
            EXEC msdb..sp_send_dbmail
              @profile_name = 'SQL_MailProfile',
              @recipients = 'somebody@somewhere.com',
              @subject='SERVERNAME  Archive Job ',
              @body='The daily Archive Job has completed successfully.'
      END


Using that sysmail_help_status_sp, we can very easily check the status of the mail FIRST, and then respond accordingly.  Take a look at this for more detail from MSFT on sysmail_help_status_sp.  




No comments:

Post a Comment