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.  

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-status-sp-transact-sql