SQL Server Service Broker: What It Is, How It Works, and How to Use It

Service Broker sits inside SQL Server, available since SQL Server 2005, and most DBAs have never used it intentionally. You have used it without knowing it because Database Mail, Service Broker, and several internal SQL Server features run on top of it. But building your own Service Broker implementations, understanding what queues, services, contracts, and routes actually are, and knowing how to troubleshoot when messages stop flowing is knowledge most DBAs never acquire.

This article changes that. It covers every component in plain English with the postal service analogy that makes the architecture stick, walks through complete working examples including the DDL schema change logging pattern, SQL Agent error alerting, and async trigger processing, and gives you the diagnostic queries and health checks to know whether your Service Broker setup is working or silently broken.

1 What Service Broker Is and Why It Exists Beginner

Service Broker is SQL Server’s built-in asynchronous messaging system. It has been part of the SQL Server engine since SQL Server 2005 and it solves a specific problem: how do you do work that takes time without making the original transaction wait for it?

Consider a trigger that fires when a row is inserted. The trigger needs to send an email, log an audit record to another database, update a summary table, and call an external web service. Every one of those operations holds the original INSERT transaction open and makes the inserting user wait. If the email server is slow or the external service times out, the original transaction times out too.

Service Broker breaks that coupling. Instead of doing the work inside the trigger, the trigger puts a message in a queue and returns immediately. The original transaction commits without waiting. A separate stored procedure reads the queue and does the actual work asynchronously, in its own transaction, at its own pace. The two operations are now decoupled.

Everything in Service Broker is transactional. Messages in queues survive server restarts. They survive database backups and restores. A message sent to a queue is not lost if the server crashes before the activation procedure processes it. This durability is what distinguishes Service Broker from a simple in-memory queue.

2 The Seven Building Blocks Explained Beginner

Service Broker has seven components that you create in a specific order. Each builds on the previous one. Understanding what each is before you build anything makes the whole system coherent.

Message Type

A message type defines the format of the data that can be sent in a message. Think of it as defining what kind of thing you can put in an envelope. You can specify that messages of this type must be valid XML (VALIDATION = WELL_FORMED_XML), must conform to a specific XML schema (VALIDATION = VALID_XML WITH SCHEMA COLLECTION), must be empty (VALIDATION = EMPTY), or can contain anything (VALIDATION = NONE). For most DBA use cases, NONE or WELL_FORMED_XML is sufficient.

Contract

A contract defines which message types can be used in a conversation and which party can send them. A contract can specify that the initiator (the sender) can send message type A, the target (the receiver) can send message type B, and either party can send message type C. Contracts enforce the protocol of communication. Think of it as the rules of the correspondence: what you can say and who gets to say it.

Queue

A queue is the mailbox. It is a database object that stores incoming messages until a stored procedure reads and processes them. Queues are transactional, durable, and live inside the database. If you back up and restore the database, messages in the queues come along. Queues can have an activation stored procedure defined on them that SQL Server automatically runs when messages arrive.

Service

A service is a named endpoint that receives messages. It is always associated with exactly one queue. When another service sends a message to a named service, SQL Server places that message in the queue associated with the target service. The service name is what you address messages to, like a street address. The queue is the actual mailbox at that address.

Route

A route tells SQL Server where to find a service. For conversations within the same database, routes are automatic and you do not need to create them. For cross-database or cross-instance conversations, you create routes that map service names to network addresses. Routes live in the msdb database as system routes or in the current database as user routes.

Conversation / Dialog

A conversation (also called a dialog) is the communication channel between two services. Every message sent or received belongs to a conversation. Conversations are always bidirectional: both the initiator and the target can send messages within the same conversation. A conversation has a unique handle (a GUID) that you use to send messages and that you must use to end the conversation when work is complete. Conversations that are never explicitly ended accumulate as orphaned conversations that consume resources.

Activation

Activation is the mechanism by which SQL Server automatically runs a stored procedure when messages arrive in a queue. You associate a stored procedure with a queue and set the maximum number of concurrent instances SQL Server can run. When messages arrive, SQL Server launches the procedure automatically. When the queue is empty, the procedure exits. You do not need polling loops or SQL Agent jobs to process messages.

3 How a Message Flows: End to End Beginner

Here is the complete journey of one message through Service Broker within a single database:

-- Complete message flow within one database:

Step 1: A trigger or stored procedure begins a conversation
        BEGIN DIALOG @handle
        FROM SERVICE [InitiatorService]
        TO SERVICE 'TargetService'
        ON CONTRACT [MyContract]
        WITH ENCRYPTION = OFF;

Step 2: A message is sent on that conversation handle
        SEND ON CONVERSATION @handle
        MESSAGE TYPE [MyMessageType]
        (@messageBody);  -- your XML or text payload

Step 3: The SEND completes within the original transaction.
        The original transaction commits.
        The message now sits in the target queue.

Step 4: SQL Server detects the message in the queue.
        If activation is configured, SQL Server runs
        the activation stored procedure automatically.

Step 5: The activation procedure reads the message:
        RECEIVE TOP(1)
            @messageType = message_type_name,
            @message     = message_body,
            @handle      = conversation_handle
        FROM TargetQueue;

Step 6: The procedure processes the message and
        ends the conversation:
        END CONVERSATION @handle;

Step 7: An EndDialog message is sent back to the
        initiator side and the conversation is closed.
        If the initiator queue is not being processed,
        these EndDialog messages accumulate -- make sure
        you process both sides of every conversation.

4 Example 1: Async DDL Schema Change Logging Intermediate

This is the pattern the user described: a DDL trigger fires on schema changes, sends a message via Service Broker, and an activation stored procedure logs the change to an audit table. This approach is better than logging directly in the DDL trigger because the trigger returns immediately without waiting for the logging operation.

-- ============================================================
-- STEP 1: Enable Service Broker on the database
-- ============================================================

ALTER DATABASE YourDatabase SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

-- Verify it is enabled
SELECT name, is_broker_enabled
FROM sys.databases
WHERE name = 'YourDatabase';

-- ============================================================
-- STEP 2: Create the audit log table
-- ============================================================

USE YourDatabase;

CREATE TABLE dbo.SchemaChangeLog (
    LogID           INT IDENTITY(1,1) PRIMARY KEY,
    EventType       NVARCHAR(100) NOT NULL,
    SchemaName      NVARCHAR(128),
    ObjectName      NVARCHAR(128),
    ObjectType      NVARCHAR(128),
    LoginName       NVARCHAR(256),
    DatabaseName    NVARCHAR(128),
    EventXML        XML,
    LoggedAt        DATETIME2 DEFAULT SYSDATETIME()
);

-- ============================================================
-- STEP 3: Create Service Broker objects
-- ============================================================

-- Message type: well-formed XML containing DDL event data
CREATE MESSAGE TYPE [//SchemaChange/EventMessage]
    VALIDATION = WELL_FORMED_XML;

-- Contract: the initiator (trigger) sends the message
CREATE CONTRACT [//SchemaChange/Contract] (
    [//SchemaChange/EventMessage] SENT BY INITIATOR
);

-- Queue: where messages will sit until processed
CREATE QUEUE dbo.SchemaChangeQueue
    WITH STATUS = ON,
         RETENTION = OFF;

-- Initiator queue and service (needed to receive EndDialog messages)
CREATE QUEUE dbo.SchemaChangeSenderQueue
    WITH STATUS = ON;

-- Target service: receives schema change messages
CREATE SERVICE [//SchemaChange/TargetService]
    ON QUEUE dbo.SchemaChangeQueue
    ([//SchemaChange/Contract]);

-- Initiator service: sends schema change messages
CREATE SERVICE [//SchemaChange/InitiatorService]
    ON QUEUE dbo.SchemaChangeSenderQueue;

-- ============================================================
-- STEP 4: Create the activation stored procedure
-- This runs automatically when messages arrive in the queue
-- ============================================================

CREATE PROCEDURE dbo.usp_ProcessSchemaChangeQueue
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @handle     UNIQUEIDENTIFIER,
        @msgType    NVARCHAR(256),
        @msgBody    XML;

    WHILE 1 = 1
    BEGIN
        BEGIN TRANSACTION;

        RECEIVE TOP(1)
            @handle  = conversation_handle,
            @msgType = message_type_name,
            @msgBody = CAST(message_body AS XML)
        FROM dbo.SchemaChangeQueue;

        IF @@ROWCOUNT = 0
        BEGIN
            ROLLBACK TRANSACTION;
            BREAK;
        END

        IF @msgType = '//SchemaChange/EventMessage'
        BEGIN
            -- Parse the DDL event XML and log to audit table
            INSERT INTO dbo.SchemaChangeLog
                (EventType, SchemaName, ObjectName, ObjectType,
                 LoginName, DatabaseName, EventXML)
            SELECT
                @msgBody.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
                @msgBody.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(128)'),
                @msgBody.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(128)'),
                @msgBody.value('(/EVENT_INSTANCE/ObjectType)[1]',  'NVARCHAR(128)'),
                @msgBody.value('(/EVENT_INSTANCE/LoginName)[1]',   'NVARCHAR(256)'),
                @msgBody.value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)'),
                @msgBody;

            END CONVERSATION @handle;
        END
        ELSE IF @msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            OR @msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
        BEGIN
            -- Always handle EndDialog and Error messages
            END CONVERSATION @handle;
        END

        COMMIT TRANSACTION;
    END
END
GO

-- ============================================================
-- STEP 5: Attach activation to the queue
-- ============================================================

ALTER QUEUE dbo.SchemaChangeQueue
    WITH ACTIVATION (
        STATUS              = ON,
        PROCEDURE_NAME      = dbo.usp_ProcessSchemaChangeQueue,
        MAX_QUEUE_READERS   = 1,   -- 1 reader for sequential processing
        EXECUTE AS OWNER
    );

-- ============================================================
-- STEP 6: Create the DDL trigger
-- This fires on schema changes and sends a Service Broker message
-- The trigger returns immediately -- no waiting for logging
-- ============================================================

CREATE TRIGGER trig_DDLSchemaChanges
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS  -- captures all DDL at database level
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @handle  UNIQUEIDENTIFIER,
        @event   XML = EVENTDATA();  -- DDL event data as XML

    -- Only capture schema-related events
    IF @event.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)')
       NOT IN ('CREATE_TABLE','ALTER_TABLE','DROP_TABLE',
               'CREATE_INDEX','DROP_INDEX','ALTER_INDEX',
               'CREATE_PROCEDURE','ALTER_PROCEDURE','DROP_PROCEDURE',
               'CREATE_VIEW','ALTER_VIEW','DROP_VIEW',
               'CREATE_FUNCTION','ALTER_FUNCTION','DROP_FUNCTION',
               'CREATE_TRIGGER','ALTER_TRIGGER','DROP_TRIGGER',
               'RENAME','CREATE_SCHEMA','DROP_SCHEMA')
        RETURN;

    -- Begin a dialog to the target service
    BEGIN DIALOG @handle
        FROM SERVICE [//SchemaChange/InitiatorService]
        TO SERVICE   '//SchemaChange/TargetService'
        ON CONTRACT  [//SchemaChange/Contract]
        WITH ENCRYPTION = OFF;

    -- Send the event data as a message
    SEND ON CONVERSATION @handle
        MESSAGE TYPE [//SchemaChange/EventMessage]
        (@event);

    -- Trigger returns. Message is queued.
    -- Activation procedure handles the logging asynchronously.
END
GO

5 Example 2: Event Notifications for Server-Level DDL Intermediate

For schema changes that span multiple databases or need to be captured at the server level, SQL Server’s Event Notification feature uses Service Broker to deliver DDL and SQL Trace events asynchronously. This is different from a DDL trigger: event notifications run outside the scope of the triggering transaction and can capture events at the server level rather than just the database level.

-- Event Notification: capture CREATE_TABLE at server level
-- using Service Broker as the delivery mechanism

USE msdb;  -- Server-level event notifications use msdb

-- Create the notification queue in msdb
CREATE QUEUE dbo.ServerDDLNotifyQueue
    WITH STATUS = ON;

-- Create the notification service
CREATE SERVICE [//Server/DDLNotifyService]
    ON QUEUE dbo.ServerDDLNotifyQueue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

-- Create the server-level event notification
-- This captures CREATE_TABLE events across ALL databases on this instance
CREATE EVENT NOTIFICATION EN_CreateTable
    ON SERVER
    FOR CREATE_TABLE
    TO SERVICE '//Server/DDLNotifyService', 'current database';

-- The notification message is XML in the same EVENTDATA() format
-- Read it from the queue using the same RECEIVE pattern:

-- Read and display recent server-level DDL events
SELECT TOP 10
    CAST(message_body AS XML)                           AS EventXML,
    CAST(message_body AS XML).value(
        '(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)')  AS EventType,
    CAST(message_body AS XML).value(
        '(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)') AS DatabaseName,
    CAST(message_body AS XML).value(
        '(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)')   AS ObjectName,
    CAST(message_body AS XML).value(
        '(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)')    AS LoginName,
    queuing_order,
    enqueue_time
FROM dbo.ServerDDLNotifyQueue
WITH (NOLOCK)  -- read without removing (just for viewing)
ORDER BY queuing_order DESC;

-- Clean up event notification when no longer needed
DROP EVENT NOTIFICATION EN_CreateTable ON SERVER;

6 Example 3: Async Trigger Processing for Heavy OLTP Intermediate

When a trigger on a high-volume OLTP table does heavy work (complex calculations, external logging, aggregation updates), it slows every INSERT, UPDATE, or DELETE on that table. Service Broker decouples the trigger work from the original DML transaction.

-- Pattern: trigger sends message, processor does the heavy work

-- The lightweight trigger (runs in milliseconds)
CREATE TRIGGER trg_Orders_AfterInsert
ON dbo.Orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @handle  UNIQUEIDENTIFIER,
        @payload XML;

    -- Build a lightweight XML payload with just the key info
    SELECT @payload = (
        SELECT
            i.OrderID,
            i.CustomerID,
            i.OrderTotal,
            i.OrderDate,
            SUSER_SNAME() AS LoginName
        FROM inserted i
        FOR XML PATH('Order'), ROOT('Orders')
    );

    -- Send to the processing queue and return immediately
    BEGIN DIALOG @handle
        FROM SERVICE [//Orders/InitiatorService]
        TO SERVICE   '//Orders/ProcessorService'
        ON CONTRACT  [//Orders/Contract]
        WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @handle
        MESSAGE TYPE [//Orders/NewOrderMessage]
        (@payload);

    -- Trigger is done. Original INSERT commits without waiting.
END
GO

-- The async processor (runs in its own transaction)
-- This is where the heavy work happens
CREATE PROCEDURE dbo.usp_ProcessOrderQueue
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @handle  UNIQUEIDENTIFIER,
        @msgType NVARCHAR(256),
        @payload XML;

    WHILE 1 = 1
    BEGIN
        BEGIN TRANSACTION;

        RECEIVE TOP(1)
            @handle  = conversation_handle,
            @msgType = message_type_name,
            @payload = CAST(message_body AS XML)
        FROM dbo.OrderProcessingQueue;

        IF @@ROWCOUNT = 0
        BEGIN
            ROLLBACK TRANSACTION;
            BREAK;
        END

        IF @msgType = '//Orders/NewOrderMessage'
        BEGIN
            -- Do the heavy work here: update aggregates,
            -- log to data warehouse, call external systems
            -- This runs in its own transaction, decoupled
            -- from the original INSERT that triggered it

            DECLARE @CustomerID INT =
                @payload.value('(/Orders/Order/CustomerID)[1]', 'INT');
            DECLARE @OrderTotal DECIMAL(18,2) =
                @payload.value('(/Orders/Order/OrderTotal)[1]', 'DECIMAL(18,2)');

            -- Update customer summary (heavy operation, safe here)
            UPDATE dbo.CustomerSummary
            SET    TotalOrders = TotalOrders + 1,
                   TotalRevenue = TotalRevenue + @OrderTotal,
                   LastOrderDate = SYSDATETIME()
            WHERE  CustomerID = @CustomerID;

            END CONVERSATION @handle;
        END
        ELSE
        BEGIN
            END CONVERSATION @handle;
        END

        COMMIT TRANSACTION;
    END
END
GO

7 Example 4: SQL Agent Error Log Alerting Intermediate

SQL Server Agent can fire alerts based on error conditions. Service Broker can be used to capture those alerts and route them to a logging table or notification queue. The most practical approach for error log alerting combines SQL Agent alerts with Service Broker event notifications.

-- ============================================================
-- Capture SQL Server error events via Event Notification
-- and log them using Service Broker
-- ============================================================

USE msdb;

-- Queue for error notifications
CREATE QUEUE dbo.ErrorAlertQueue
    WITH STATUS = ON;

-- Service for error alerts
CREATE SERVICE [//Alerts/ErrorNotifyService]
    ON QUEUE dbo.ErrorAlertQueue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

-- Error log table
USE YourDatabase;
CREATE TABLE dbo.SQLServerErrorLog (
    ErrorLogID      INT IDENTITY(1,1) PRIMARY KEY,
    EventType       NVARCHAR(100),
    Severity        INT,
    ErrorNumber     INT,
    ErrorMessage    NVARCHAR(MAX),
    LoginName       NVARCHAR(256),
    HostName        NVARCHAR(256),
    CapturedAt      DATETIME2 DEFAULT SYSDATETIME(),
    EventXML        XML
);

-- Activation procedure to process error alerts
CREATE PROCEDURE dbo.usp_ProcessErrorAlerts
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @handle  UNIQUEIDENTIFIER,
        @msgType NVARCHAR(256),
        @event   XML;

    WHILE 1 = 1
    BEGIN
        BEGIN TRANSACTION;

        RECEIVE TOP(1)
            @handle  = conversation_handle,
            @msgType = message_type_name,
            @event   = CAST(message_body AS XML)
        FROM msdb.dbo.ErrorAlertQueue;

        IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION; BREAK; END

        IF @msgType = 'http://schemas.microsoft.com/SQL/Notifications/PostEventNotification'
        BEGIN
            INSERT INTO YourDatabase.dbo.SQLServerErrorLog
                (EventType, Severity, ErrorNumber, ErrorMessage,
                 LoginName, HostName, EventXML)
            SELECT
                @event.value('(/EVENT_INSTANCE/EventType)[1]',  'NVARCHAR(100)'),
                @event.value('(/EVENT_INSTANCE/Severity)[1]',   'INT'),
                @event.value('(/EVENT_INSTANCE/Error)[1]',      'INT'),
                @event.value('(/EVENT_INSTANCE/TextData)[1]',   'NVARCHAR(MAX)'),
                @event.value('(/EVENT_INSTANCE/LoginName)[1]',  'NVARCHAR(256)'),
                @event.value('(/EVENT_INSTANCE/HostName)[1]',   'NVARCHAR(256)'),
                @event;

            END CONVERSATION @handle;
        END
        ELSE
            END CONVERSATION @handle;

        COMMIT TRANSACTION;
    END
END
GO

-- Attach activation to the error queue
ALTER QUEUE msdb.dbo.ErrorAlertQueue
    WITH ACTIVATION (
        STATUS            = ON,
        PROCEDURE_NAME    = YourDatabase.dbo.usp_ProcessErrorAlerts,
        MAX_QUEUE_READERS = 1,
        EXECUTE AS OWNER
    );

-- Create the event notification for severity 17+ errors
-- (Severity 17+ are resource errors worth logging)
CREATE EVENT NOTIFICATION EN_ErrorSeverity17Plus
    ON SERVER
    FOR ERRORLOG
    TO SERVICE '//Alerts/ErrorNotifyService', 'current database'
    WHERE severity >= 17;

8 Checking Whether Service Broker Is Working Beginner

Service Broker can fail silently. Messages pile up in a queue with nothing processing them and no error appears in SQL Server Management Studio. These are the first diagnostic queries to run when something seems wrong.

-- ============================================================
-- CHECK 1: Is Service Broker enabled on the database?
-- ============================================================

SELECT name, is_broker_enabled, service_broker_guid
FROM sys.databases
WHERE name = DB_NAME();

-- is_broker_enabled = 0 means NOTHING in Service Broker
-- on this database will work. Enable it first:
-- ALTER DATABASE YourDatabase SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

-- ============================================================
-- CHECK 2: Queue health -- are queues active and how many
-- messages are waiting?
-- ============================================================

SELECT
    name                    AS QueueName,
    is_receive_enabled,
    is_enqueue_enabled,
    activation_procedure,
    is_activation_enabled,
    max_readers,
    -- Count messages currently in the queue
    (SELECT COUNT(*)
     FROM sys.service_broker_endpoints) AS BrokerEndpoints
FROM sys.service_queues
WHERE is_ms_shipped = 0  -- user-created queues only
ORDER BY name;

-- Check actual message counts per queue
SELECT
    q.name                  AS QueueName,
    q.is_receive_enabled,
    q.is_enqueue_enabled,
    q.is_activation_enabled,
    q.max_readers,
    -- Message count (requires querying the queue object itself)
    OBJECT_NAME(q.object_id) AS QueueObjectName
FROM sys.service_queues q
WHERE q.is_ms_shipped = 0;

-- Count messages in a specific queue
SELECT COUNT(*) AS MessageCount
FROM dbo.SchemaChangeQueue WITH (NOLOCK);

-- ============================================================
-- CHECK 3: Are there messages stuck in the transmission queue?
-- (Only relevant for cross-database or cross-instance messaging)
-- ============================================================

SELECT
    to_service_name,
    to_broker_instance,
    message_type_name,
    transmission_status,    -- NULL = OK, error text = problem
    enqueue_time,
    DATEDIFF(MINUTE, enqueue_time, GETDATE()) AS MinutesWaiting
FROM sys.transmission_queue
ORDER BY enqueue_time;

-- Non-null transmission_status means messages cannot be delivered
-- Common causes: route missing, broker not enabled on target,
-- network issue for cross-instance

-- ============================================================
-- CHECK 4: Conversation state -- any conversations in bad states?
-- ============================================================

SELECT
    far_service,
    state_desc,
    COUNT(*)                AS ConversationCount,
    MAX(DATEDIFF(HOUR, start_time, GETDATE())) AS MaxAgeHours
FROM sys.conversation_endpoints
GROUP BY far_service, state_desc
ORDER BY state_desc, ConversationCount DESC;

-- States to watch for:
-- STARTED_OUTBOUND (SO): conversation started, waiting for response
-- CONVERSING (CO): active and healthy
-- DISCONNECTED_INBOUND (DI): partner disconnected, needs cleanup
-- CLOSED (CD): finished, should be cleaned up
-- ERROR (ER): error occurred, end this conversation

9 Poison Messages: The Silent Queue Killer Intermediate

A poison message is a message that causes the activation stored procedure to fail and roll back the transaction. When the same transaction rolls back five times trying to process the same message, SQL Server automatically disables the queue by setting is_receive_enabled = 0. Everything stops. New messages cannot be received. No error is raised to your application. The queue simply goes silent.

This is the most common Service Broker production problem and it is entirely preventable with proper error handling in the activation procedure.

-- ============================================================
-- DETECT a disabled queue
-- ============================================================

SELECT name, is_receive_enabled, is_enqueue_enabled
FROM sys.service_queues
WHERE is_receive_enabled = 0   -- queue is disabled
AND   is_ms_shipped = 0;

-- If you find a disabled queue, there is a poison message in it

-- ============================================================
-- FIND the poison message
-- ============================================================

-- Peek at the messages in the disabled queue without removing them
SELECT TOP 10
    conversation_handle,
    message_type_name,
    CAST(message_body AS NVARCHAR(MAX)) AS MessageBody,
    message_sequence_number,
    enqueue_time
FROM dbo.SchemaChangeQueue WITH (NOLOCK)
ORDER BY enqueue_time;

-- ============================================================
-- RESOLVE the poison message
-- ============================================================

-- Step 1: Re-enable the queue
ALTER QUEUE dbo.SchemaChangeQueue WITH STATUS = ON;

-- Step 2: Receive and end the conversation for the bad message
-- Do this in a separate transaction to isolate it
DECLARE @handle UNIQUEIDENTIFIER;
DECLARE @msgType NVARCHAR(256);

BEGIN TRANSACTION;

RECEIVE TOP(1)
    @handle  = conversation_handle,
    @msgType = message_type_name
FROM dbo.SchemaChangeQueue;

-- End the conversation with an error to notify the sender
END CONVERSATION @handle
    WITH ERROR = 500
    DESCRIPTION = N'Poison message detected and removed';

COMMIT TRANSACTION;

-- ============================================================
-- PREVENT poison messages with proper error handling
-- in the activation procedure
-- ============================================================

-- The activation procedure should handle errors gracefully
-- rather than rolling back and re-queuing the same message

CREATE PROCEDURE dbo.usp_SafeQueueProcessor
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @handle  UNIQUEIDENTIFIER;
    DECLARE @msgType NVARCHAR(256);
    DECLARE @body    XML;

    WHILE 1 = 1
    BEGIN
        BEGIN TRANSACTION;

        BEGIN TRY
            RECEIVE TOP(1)
                @handle  = conversation_handle,
                @msgType = message_type_name,
                @body    = CAST(message_body AS XML)
            FROM dbo.SchemaChangeQueue;

            IF @@ROWCOUNT = 0
            BEGIN
                ROLLBACK TRANSACTION;
                BREAK;
            END

            -- Process the message here...
            -- If this fails it goes to the CATCH block

            IF @msgType = '//SchemaChange/EventMessage'
                -- Do work here
                END CONVERSATION @handle;
            ELSE
                END CONVERSATION @handle;

            COMMIT TRANSACTION;

        END TRY
        BEGIN CATCH
            -- On error: end the conversation with error
            -- rather than rolling back and requeueing
            -- This prevents the poison message cycle
            IF @@TRANCOUNT > 0
            BEGIN
                END CONVERSATION @handle
                    WITH ERROR = ERROR_NUMBER()
                    DESCRIPTION = ERROR_MESSAGE();
                COMMIT TRANSACTION;  -- commit the END CONVERSATION
            END

            -- Log the error for investigation
            INSERT INTO dbo.ServiceBrokerErrorLog
                (QueueName, ErrorNumber, ErrorMessage, ConversationHandle, LoggedAt)
            VALUES
                ('SchemaChangeQueue', ERROR_NUMBER(),
                 ERROR_MESSAGE(), @handle, SYSDATETIME());
        END CATCH
    END
END
GO

10 Stuck Conversations and How to Clean Them Up Intermediate

Every conversation must be explicitly ended. If the activation procedure ends the target side of a conversation but nothing processes the corresponding EndDialog message on the initiator side, those conversations accumulate as orphaned endpoints. Over months this can grow to thousands of stale conversation endpoints consuming memory and appearing in diagnostic queries.

-- ============================================================
-- FIND orphaned and stale conversations
-- ============================================================

SELECT
    conversation_handle,
    state_desc,
    far_service,
    start_time,
    DATEDIFF(HOUR, start_time, GETDATE()) AS AgeHours,
    is_initiator
FROM sys.conversation_endpoints
WHERE state_desc IN ('DISCONNECTED_INBOUND', 'CLOSED', 'ERROR')
OR    DATEDIFF(DAY, start_time, GETDATE()) > 7  -- older than 7 days
ORDER BY start_time;

-- ============================================================
-- CLEAN UP orphaned conversations
-- Do this in batches to avoid long-running transactions
-- ============================================================

DECLARE @handle UNIQUEIDENTIFIER;
DECLARE @count  INT = 0;

DECLARE cleanup_cursor CURSOR FAST_FORWARD FOR
    SELECT conversation_handle
    FROM sys.conversation_endpoints
    WHERE state_desc IN ('DISCONNECTED_INBOUND', 'CLOSED', 'ERROR')
    OR    DATEDIFF(DAY, start_time, GETDATE()) > 30;

OPEN cleanup_cursor;
FETCH NEXT FROM cleanup_cursor INTO @handle;

WHILE @@FETCH_STATUS = 0 AND @count < 1000  -- batch limit
BEGIN
    BEGIN TRY
        END CONVERSATION @handle WITH CLEANUP;
        SET @count += 1;
    END TRY
    BEGIN CATCH
        -- Some conversations may already be gone
    END CATCH
    FETCH NEXT FROM cleanup_cursor INTO @handle;
END

CLOSE cleanup_cursor;
DEALLOCATE cleanup_cursor;

PRINT CAST(@count AS VARCHAR) + ' conversations cleaned up.';

-- ============================================================
-- Handle EndDialog messages on the initiator queue
-- If you never process these, they accumulate
-- ============================================================

DECLARE @handle UNIQUEIDENTIFIER;
DECLARE @msgType NVARCHAR(256);

-- Drain the initiator queue of EndDialog messages
WHILE 1 = 1
BEGIN
    BEGIN TRANSACTION;

    RECEIVE TOP(10)
        @handle  = conversation_handle,
        @msgType = message_type_name
    FROM dbo.SchemaChangeSenderQueue;  -- initiator queue

    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION;
        BREAK;
    END

    END CONVERSATION @handle;
    COMMIT TRANSACTION;
END

11 The Complete Troubleshooting Query Toolkit Beginner

Run these queries when investigating any Service Broker issue. They cover every layer from database configuration to individual message state.

-- ============================================================
-- FULL SERVICE BROKER HEALTH CHECK
-- Run this whenever something seems wrong
-- ============================================================

-- 1. Database-level broker status
SELECT name, is_broker_enabled, service_broker_guid
FROM sys.databases
ORDER BY name;

-- 2. All user queues: status and message counts
SELECT
    q.name                          AS QueueName,
    q.is_receive_enabled,
    q.is_enqueue_enabled,
    q.is_activation_enabled,
    q.max_readers,
    q.activation_procedure,
    -- Count messages in each queue
    (SELECT COUNT(*) FROM sys.objects o2
     WHERE o2.object_id = q.object_id) AS ConfirmExists
FROM sys.service_queues q
WHERE q.is_ms_shipped = 0
ORDER BY q.name;

-- 3. All services and which queue they use
SELECT
    s.name                          AS ServiceName,
    q.name                          AS QueueName,
    q.is_receive_enabled
FROM sys.services s
JOIN sys.service_queues q ON q.object_id = s.service_queue_id
WHERE s.principal_id IS NOT NULL  -- user-created services
ORDER BY s.name;

-- 4. All contracts and message types
SELECT
    c.name                          AS ContractName,
    mt.name                         AS MessageTypeName,
    cmt.is_sent_by_initiator,
    cmt.is_sent_by_target
FROM sys.service_contracts c
JOIN sys.service_contract_message_usages cmt
    ON cmt.service_contract_id = c.service_contract_id
JOIN sys.service_message_types mt
    ON mt.message_type_id = cmt.message_type_id
WHERE c.principal_id IS NOT NULL  -- user-created contracts
ORDER BY c.name, mt.name;

-- 5. Conversation endpoint summary
SELECT
    state_desc,
    COUNT(*)                        AS Count,
    MIN(start_time)                 AS OldestConversation,
    MAX(start_time)                 AS NewestConversation
FROM sys.conversation_endpoints
GROUP BY state_desc
ORDER BY Count DESC;

-- 6. Transmission queue: messages that could not be delivered
SELECT
    to_service_name,
    transmission_status,
    message_type_name,
    DATEDIFF(MINUTE, enqueue_time, GETDATE()) AS MinutesStuck
FROM sys.transmission_queue
ORDER BY enqueue_time;

-- 7. Routes
SELECT name, remote_service_name, broker_instance, address
FROM sys.routes
ORDER BY name;

-- 8. Recent Service Broker events in error log
EXEC xp_readerrorlog 0, 1, N'broker', NULL, NULL, NULL, N'desc';

References

• Microsoft Docs: Service Broker Overview
• Microsoft Docs: Typical Uses of Service Broker
• Microsoft Docs: Event Notifications
• Microsoft Docs: Removing Poison Messages from a Queue
• Microsoft Docs: sys.service_queues
• Microsoft Docs: sys.conversation_endpoints
• Microsoft Docs: sys.transmission_queue
• SQLPerformance.com: Configuring Service Broker for Asynchronous Processing
• SQLYARD: SQL Server Performance Tuning Complete Guide
• SQLYARD: SQL Server Blocking vs Deadlocks