Asynchronous email delivery via Service Broker
Classic scenario: you have a stored procedure that does something (creates an order, registers a user, whatever) and at the end an email needs to go out. Instead of calling sp_send_dbmail directly (which blocks the caller until SMTP has responded), you put the mail request into a queue and a worker sends it in the background.
One-time Setup
-- Demo DB
CREATE DATABASE MailDemo;
GO
ALTER DATABASE MailDemo SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
USE MailDemo;
GO
-- Message Type: simply XML with mail data
CREATE MESSAGE TYPE [//demo/MailRequest]
VALIDATION = WELL_FORMED_XML;
-- Contract: Initiator sends MailRequest
CREATE CONTRACT [//demo/MailContract]
([//demo/MailRequest] SENT BY INITIATOR);
-- Target Queue (where mail requests land)
CREATE QUEUE MailTargetQueue;
-- Target Service (receiver)
CREATE SERVICE [//demo/MailTarget]
ON QUEUE MailTargetQueue
([//demo/MailContract]);
-- Initiator Queue (for replies / EndDialog messages)
CREATE QUEUE MailInitQueue;
CREATE SERVICE [//demo/MailInit]
ON QUEUE MailInitQueue;
GOHelper Procedure for Sending
This is how your business logic triggers the mail send — fast, transactional, no SMTP wait time:
CREATE OR ALTER PROCEDURE dbo.QueueMail
@To NVARCHAR(200),
@Subject NVARCHAR(400),
@Body NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @h UNIQUEIDENTIFIER;
DECLARE @msg XML = (
SELECT
@To AS [to],
@Subject AS [subject],
@Body AS [body]
FOR XML PATH('mail'), TYPE
);
BEGIN DIALOG CONVERSATION @h
FROM SERVICE [//demo/MailInit]
TO SERVICE '//demo/MailTarget'
ON CONTRACT [//demo/MailContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @h
MESSAGE TYPE [//demo/MailRequest] (@msg);
END;
GOWorker Procedure (Activated Automatically)
This procedure reads messages from the queue and calls sp_send_dbmail:
CREATE OR ALTER PROCEDURE dbo.ProcessMailQueue
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@h UNIQUEIDENTIFIER,
@msgType SYSNAME,
@msgBody XML,
@to NVARCHAR(200),
@subject NVARCHAR(400),
@body NVARCHAR(MAX);
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION;
WAITFOR (
RECEIVE TOP(1)
@h = conversation_handle,
@msgType = message_type_name,
@msgBody = CAST(message_body AS XML)
FROM MailTargetQueue
), TIMEOUT 5000;
-- Queue empty -> exit
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END;
IF @msgType = N'//demo/MailRequest'
BEGIN
SELECT
@to = @msgBody.value('(/mail/to)[1]', 'NVARCHAR(200)'),
@subject = @msgBody.value('(/mail/subject)[1]', 'NVARCHAR(400)'),
@body = @msgBody.value('(/mail/body)[1]', 'NVARCHAR(MAX)');
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DefaultMailProfile', -- you need to create this
@recipients = @to,
@subject = @subject,
@body = @body,
@body_format = 'HTML';
END CONVERSATION @h;
END TRY
BEGIN CATCH
-- Logging so you can see poison messages
INSERT dbo.MailErrorLog (ConversationHandle, ErrorMsg, MsgBody)
VALUES (@h, ERROR_MESSAGE(), CAST(@msgBody AS NVARCHAR(MAX)));
END CONVERSATION @h WITH ERROR = 50001
DESCRIPTION = N'Mail send failed';
END CATCH;
END
ELSE IF @msgType IN
(N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
END CONVERSATION @h;
END;
COMMIT TRANSACTION;
END;
END;
GO
CREATE TABLE dbo.MailErrorLog (
Id INT IDENTITY PRIMARY KEY,
ConversationHandle UNIQUEIDENTIFIER,
ErrorMsg NVARCHAR(4000),
MsgBody NVARCHAR(MAX),
CreatedAt DATETIME2 DEFAULT SYSDATETIME()
);
GOEnable Activation — This Is the Key Part
Now you tell the queue: "When messages arrive, automatically start up to 4 workers in parallel."
ALTER QUEUE MailTargetQueue
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = dbo.ProcessMailQueue,
MAX_QUEUE_READERS = 4,
EXECUTE AS SELF
);
GOFrom here on you don't need a Windows service, no SQL Agent job, no nothing. SQL Server monitors the queue itself and fires up the worker procedure.
Usage in Your Actual Stored Procedure
CREATE OR ALTER PROCEDURE dbo.RegisterUser
@UserName NVARCHAR(100),
@Email NVARCHAR(200)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
INSERT dbo.Users (UserName, Email)
VALUES (@UserName, @Email);
-- Put mail in queue – returns immediately
EXEC dbo.QueueMail
@To = @Email,
@Subject = N'Welcome!',
@Body = N'<h1>Hello</h1><p>Your account is ready.</p>';
COMMIT; -- Insert AND mail request are atomic
END;
GOWhat's Really Elegant Here
Transactional consistency. If the INSERT rolls back, the mail request disappears from the queue as well. You can never end up in a situation where "the user wasn't created, but the welcome mail went out anyway." Achieving this with an external queue (RabbitMQ etc.) requires significantly more effort.
Caller performance. RegisterUser returns after a few milliseconds, regardless of whether the SMTP server is currently slow or takes 30 seconds.
Automatic scaling. MAX_QUEUE_READERS = 4 means: under heavy load up to 4 workers run in parallel, under light load it scales down automatically.
Built-in poison message protection. If the worker procedure rolls back 5 times in a row, SQL Server automatically disables the queue (STATUS = OFF) to prevent endless retries.
Monitoring
-- Current messages in the queue
SELECT * FROM MailTargetQueue WITH (NOLOCK);
-- Stuck outbound messages
SELECT * FROM sys.transmission_queue;
-- Open conversations
SELECT * FROM sys.conversation_endpoints
WHERE state <> 'CD' -- CD = Closed/Disconnected
ORDER BY security_timestamp DESC;
-- Queue status (is activation still on?)
SELECT name, is_activation_enabled, activation_procedure
FROM sys.service_queues
WHERE name = 'MailTargetQueue';Test
EXEC dbo.QueueMail
@To = N'georg@example.com',
@Subject = N'Test',
@Body = N'<p>Service Broker rocks</p>';
-- Should be processed almost immediately
WAITFOR DELAY '00:00:02';
SELECT COUNT(*) AS RemainingInQueue FROM MailTargetQueue WITH (NOLOCK);The same pattern works for anything you want to do asynchronously: HTTP calls via sp_OACreate or CLR, file operations, cache invalidations, logging to external systems. You just swap out the EXEC sp_send_dbmail block in the worker procedure.
If you're interested, I can show you next the pattern with multiple message types per contract (e.g. mail + SMS + webhook on the same queue) or how to build scheduled/delayed messages — Service Broker has BEGIN CONVERSATION TIMER for that, which is nice for retry logic.