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.  




3 comments:

  1. Hmmmm, have you tried any program to settle it? I needed to move my entire message archive from Microsoft Outlook to another email program and had no idea how to preserve all folders and attachments. Luckily, I’ve found an utility that helped me to export email from outlook easily find more details. By the way, it may also export contacts from outlook, export outlook address book and even calendar from outlook. Hope it will help you too!

    ReplyDelete
  2. My pleasure, Richard, and thank you, Fred! That is very helpful!

    ReplyDelete