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_
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.
