Troubleshooting Routing and Message Delivery
This section provides suggestions for correcting common symptoms related to routing and message delivery.
Symptom: Messages Remain in the Transmission Queue
Ensure that Service Broker message delivery is activated in the database. The is_broker_enabled column of sys.databases shows whether broker message delivery is activated, as shown in the sample below:
SELECT is_broker_enabled FROM sys.databases
WHERE database_id = DB_ID() ;
Broker message delivery can be deactivated to prevent messages from being delivered to the wrong database. For more information about Service Broker message delivery, see Managing Service Broker Identities. For more information about activating Service Broker message delivery, see How to: Activate Service Broker Message Delivery in Databases (Transact-SQL).
If Service Broker message delivery is active, check the transmission_status column in the sys.transmission_queue catalog view for the messages. Common error messages include:
| Message | Description |
|---|---|
No route for service. |
Service Broker could not locate a route to the service specified. |
The target service broker is unreachable. |
Service Broker could not deliver the message to the target service broker. |
Transport layer is unavailable. |
No Service Broker endpoint exists in the instance, or the Service Broker endpoint did not start successfully. |
Target queue is disabled. |
The queue that the destination service uses has the STATUS option set to OFF. Service Broker does not add new messages to a queue with a STATUS of OFF. |
An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'. |
The remote side of the conversation accepted the TCP/IP connection but closed the connection before allowing a message to be sent. |
(None) |
Service Broker has not yet attempted to send the message. This may indicate that Service Broker message delivery is not activated in the database. |
Symptom: Route Exists, Transmission Status Shows No Route For Service
If the route was created after the message, the most common cause for this symptom is that the message has not been retried after the route was created. For more information about retries, see Service Broker Routing and Networking.
Check to make sure that the service name specified in the message exactly matches the service name specified in the route. Service Broker uses a byte-by-byte binary comparison to match service names. If a route that specifies the service name exists, one way to check this comparison is to run a query similar to the following query:
SELECT N'No Exact Match' = tq.to_service_name
FROM sys.transmission_queue AS tq
WHERE NOT EXISTS
(SELECT remote_service_name
FROM sys.routes AS routes
WHERE tq.to_service_name = routes.remote_service_name) ;
The service names that appear in the result set do not have an exact match in the routing table for the database. Notice that a route that does not specify a service name will match any service name. For more information about routing, see Service Broker Routing.
If the message specifies a broker instance identifier, check that the route specifies the same broker instance identifier or that the route does not specify a broker instance identifier.
Check that the route has not expired. The lifetime column of the sys.routes catalog view contains the expiration date and time for the route.
Symptom: Transmission Status Shows Target Service Broker Unreachable
The destination did not accept the message. This may indicate that the service name specified does not match the name of a service that the destination SQL Server instance hosts, or that the destination does not contain a route for the service. To troubleshoot this problem, check the routing and service configuration for the destination.
Symptom: Transmission Status Shows Transport Layer Unavailable
Check to see if a Service Broker endpoint exists, and that the state of the endpoint is STARTED. If not, create an endpoint. For more information about Service Broker endpoints, see Service Broker Endpoints. For more information about creating an endpoint, see How to: Activate Service Broker Networking (Transact-SQL).
Symptom: Transmission Status Shows 'An Existing Connection Was Forcibly Closed By the Remote Host'
Transport security may be incorrectly configured, or the TCP/IP address for the route specifies a port that is in use by a service other than Service Broker. Notice that Service Broker does not use the Tabular Data Stream protocol. The route must specify the port that corresponds to the port used by the Service Broker endpoint on the remote SQL Server instance.
Check the Service Broker endpoint configuration to ensure that the two instances have compatible network security settings. If the Service Broker endpoint for one instance specifies REQUIRED or ENABLED, the Service Broker endpoint for the other instance cannot specify NONE.
Check certificates, users, and permissions for Service Broker transport security. Configuration for transport security is explained in the topic Service Broker Transport Security.
See Also
Tasks
How to: Activate Service Broker Networking (Transact-SQL)
Concepts
Service Broker Routing and Networking
Service Broker Endpoints
Service Broker Routing
Starting and Stopping the Queue
Other Resources
sys.databases (Transact-SQL)
sys.transmission_queue (Transact-SQL)
sys.routes (Transact-SQL)
sys.service_broker_endpoints (Transact-SQL)