Error 14641 Databse Mail is stopped

Found this fun error today.

Msg 14641, Level 16, State 1, Procedure sp_send_dbmail, Line 81
Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.

I first tried starting Database mail with this command:

EXEC msdb.dbo.sysmail_start_sp; 

Database mail was started so I kicked off my job again and received the Msg 14641. I executed the status command and noticed Database Mail was stopped again.

EXEC msdb.dbo.sysmail_help_status_sp;

Odd. After some digging I found this command in the sqlservercentral.com forum and it worked.

--First start databse mail again
EXEC msdb.dbo.sysmail_start_sp;

SET NOCOUNT ON
DECLARE @ch uniqueidentifier;
DECLARE @message_type nvarchar(256);
DECLARE @message_body nvarchar(max);
DECLARE @ctr bigint;
DECLARE @ctr2 bigint;
WHILE (1=1)
  BEGIN
     RECEIVE @ch = CONVERSATION_HANDLE, @message_type = message_type_name FROM ExternalMailQueue
     SET @ctr2 = @ctr2 + 1
     SET @ctr = (SELECT COUNT(*) FROM ExternalMailQueue)
     IF @ctr = 0
       BREAK
  END

This will loop through the ExternalMailQueue using the RECEIVE command and clears out the messages in the queue. It seems I had some messages backed up in the mail queue causing Database Mail to crash.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s