sql server sp_send_dbmail

Question

I am using SQL Server's sp_send_dbmail stored procedure to send mail via the database.

But when I execute that procedure it's shutting down the db mail procedure. I tried to start it again by sysmail_start_sp. But it's again shutting down in 7-8 seconds.

I tried with the code below:

EXEC msdb..sp_send_dbmail @profile_name = 'Test',
@recipients = '[email protected]',
@subject = 'test mail',
@body_format = 'HTML',
@body = 'test mail',
@from_address = '[email protected]'

When I tried to check the error log I found the below error:

Message:

The read on the database failed. Reason: The error description is 'Whitespace is not allowed at this location.'.Data: System.Collections.ListDictionaryInternalTargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)HelpLink: NULLSource: DatabaseMailEngineStackTrace Information===================

Also when I checked sql email log history I found this error:

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

Answer 1

Whenever you try to send Test mail from Database mail; it throws below error message:

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

---

1. First of all make sure that Service Broker Message Delivery in Databases is enabled by executing the following command in SSMS:

   SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
   

   If the result of above query is 0, then activate the service broker.

   Activating Service Broker allows messages to be delivered to the database. A Service Broker endpoint must be created to send and receive messages from outside of the instance.

   To activate Service Broker in a database use the following command:

   USE master ;
   GO
   
   ALTER DATABASE DatabaseName SET ENABLE_BROKER ;
   GO
   

2. If the Service Broker is enabled then confirm whether Database Mail is enabled or not by executing below queries in SQL Server Management Studio:

   sp_configure 'show advanced', 1
   GO
   
   RECONFIGURE
   GO
   
   sp_configure
   GO
   

   If the result set shows run_value as 1 then Database Mail is enabled.

3. If the Database Mail option is disabled then run the below queries to enable it:

   sp_configure 'Database Mail XPs', 1; 
   GO
   
   RECONFIGURE;
   GO
   
   sp_configure 'show advanced', 0; 
   GO
   
   RECONFIGURE;
   GO
   

4. Once the Database Mail is enabled then to start Database Mail External Program use the below mentioned query on msdb database:

   USE msdb ;       
   EXEC msdb.dbo.sysmail_start_sp;
   

5. To confirm that Database Mail External Program is started, run the query mentioned below :

   EXEC msdb.dbo.sysmail_help_status_sp;
   

6. If the Database Mail external program is started then check the status of mail queue using below statement:

   EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';