Post
Ask Question

Solution: Mail not queued. Database Mail is stopped

Problem Statement

Executing the query “EXEC msdb.dbo.sp_send_dbmail ” failed with the following error: “Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.

Solution

Sometimes SQL Server Mail may encounter problems and fail to send emails, leaving them in the Outbox folder without being queued. In this article, we will explore some common reasons why SQL Server Mail may not be queued and what steps can be taken to troubleshoot the issue.

One of the most common reasons why SQL Server Mail may not be queued is because the Database Mail system has been stopped, as clearly mentioned in the error. This can occur for a variety of reasons, including system updates or changes to the database configuration. To check if this is the case, follow the below steps.

Step 1. Check Service Broker is enabled or not by executing the following SQL query.

SELECT 
    CASE 
        WHEN is_broker_enabled = 1 THEN 'Enabled' 
        ELSE 'Disabled' 
    END AS ServiceBrokerStatus
FROM 
    sys. databases 
WHERE 
    name = 'msdb';

Step 2. If Service Broker is disabled, enable it by executing the following SQL query.

USE master;
GO
ALTER DATABASE DatabaseName
SET ENABLE_BROKER;

Step 3. If Service Broker is enabled, then check whether Database Mail is enabled or not by executing the following query. If the result is 1, then it is enabled.

-- Enable advanced options
sp_configure 'show advanced options', 1;
GO
-- Apply configuration change
RECONFIGURE;
GO
-- Check the current configuration settings
sp_configure;
GO

Step 4. If Database Mail is not enabled, then execute the following query to enable it.

-- Enable Database Mail XPs
sp_configure 'Database Mail XPs', 1;
GO
-- Apply configuration change
RECONFIGURE;
GO
-- Disable showing advanced options
sp_configure 'show advanced options', 0;
GO
-- Apply configuration change
RECONFIGURE;
GO

Step 5. Once the Database Mail is enabled, execute the following query to start the Database Mail by starting the Service Broker objects that the external program uses.

-- Execute the stored procedure to start SQL Server Database Mail
EXEC msdb.dbo.sysmail_start_sp;

Step 6. To ensure whether the Database Mail is started, run the below SQL query to check the status.

EXEC msdb.dbo.sysmail_help_status_sp

Conclusion

SQL Server Mail is a useful tool for sending emails from within the database server, but it is important to be aware of common issues that can occur. By following the steps outlined in this article, users can troubleshoot the issue of “Mail not queued. Database mail is stopped” in SQL Server and ensures that important emails are sent out in a timely and efficient manner.


Similar Articles