This section provides a list of system catalog views, dynamic management views, Profiler trace events, and Performance Monitor counters that you can use to monitor and tune a Service Broker application.
Table 15.1 lists Service Broker catalog views.
Table 15.1. Service Broker Catalog Views
This catalog view contains one row per message type registered in the service broker.
This catalog view contains one row for each contract in the database.
This catalog view contains one row for each service in the database.
This catalog view contains one row for the Service Broker endpoint.
This catalog view contains one row per remote service binding.
This catalog view contains one row for each message in the transmission queue.
This catalog views contains one row per route.
This catalog view contains one row per (service, queue) pair.
This catalog view contains one row per (contract, message type) pair.
This catalog view contains one row per (service, contract) pair.
This catalog view contains one row for each conversation group.
Each side of a Service Broker conversation is represented by a conversation endpoint. This catalog view contains one row per conversation endpoint in the database.
Table 15.2 lists dynamic management views (DMVs) that are useful for monitoring Service Broker activity.
Table 15.2. Service Broker DMVs
This DMV contains a row for each stored procedure activated by Service Broker.
This DMV contains a row for each Service Broker network connection.
This DMV contains a row for each Service Broker message that the SQL Server instance is in the process of forwarding.
This DMV contains a row for each queue monitor in the instance. A queue monitor manages activation for a queue.
If you want to force-remove all the messages from the transmission queue, the quickest solution is to run the following T-SQL statement:
ALTER DATABASE db_name SET NEW_BROKER
However, be very careful in running this command as it unconditionally ends all dialogs. The specified database receives a new broker identifier, and all existing conversations in the database are immediately removed without producing end dialog messages. Instead, you should selectively get rid of the dialogs. For instance, the following T-SQL script creates a cursor over the sys.conversation_endpoints catalog view to end conversations that are in error states:
DECLARE @convHandle UNIQUEIDENTIFIER; DECLARE cursorCE CURSOR FOR SELECT conversation_handle FROM sys.conversation_endpoints WHERE state = 'ER'; OPEN cursorCE; FETCH NEXT FROM cursorCE INTO @convHandle; WHILE @@FETCH_STATUS = 0 BEGIN END CONVERSATION @convHandle WITH CLEANUP; FETCH NEXT FROM cursorCE INTO @convHandle; End CLOSE cursorCE; DEALLOCATE cursorCE; GO
The state column in the sys.conversation_endpoints catalog view indicates the current state of the conversation, and it can have values such as SO for started outbound, SI for started inbound, CO for conversing, DI for disconnected inbound, DO for disconnected outbound, ER for error, and CD for closed.
Table 15.3 lists Profiler trace events that are useful for monitoring Service Broker activity.
Table 15.3. Service Broker Profiler Trace Events
Occurs when a queue monitor starts an activation stored procedure or sends a QUEUE_ACTIVATION notification, or when an activation stored procedure started by a queue monitor exits.
Reports the status of a transport connection managed by Service Broker.
Reports the progress of a Service Broker conversation.
Occurs when Service Broker creates a new conversation group or drops an existing conversation group.
Occurs when Service Broker receives a corrupted message.
Broker:Forwarded Message Dropped
Occurs when Service Broker drops a message that was intended to be forwarded.
Broker:Forwarded Message Sent
Occurs when Service Broker forwards a message.
Occurs when Service Broker determines the routing for a message. A message is classified for local, remote, or delayed delivery.
Occurs when Service Broker is unable to retain a received message that should have been delivered to a service in this instance.
Indicates that a poison message was detected because there were five transaction rollbacks in a row on a Service Broker queue. The event contains the database ID and queue ID of the queue that contains the poison message.
Broker:Remote Message Acknowledgement
Indicates when an acknowledgement has been received.
Indicates that errors have occurred in the Service Broker transport layer. The error number and state values indicate the source of the error.
Table 15.4 lists Performance Monitor counters that are useful for monitoring Service Broker activity. For a default instance, the Performance Monitor object name begins with SQLServer:, and for a named instance, the Performance Monitor object name begins with MSSQL$<instance_name>:.
Table 15.4. Service Broker Performance Monitor Counters
Contains Performance Monitor counters that report information about stored procedure activation, such as Stored Procedures Invoked/sec, Tasks Running, and so on.
Contains Performance Monitor counters that report general Service Broker information, such as SQL SENDs/sec, SQL SEND Total, SQL RECEIVEs/sec, SQL RECEIVE Total, Forwarded Messages/sec, and so on.
Broker / DBM Transport
Contains Performance Monitor counters that report information related to Service Broker and database mirroring network activity. This category includes counters such as Open Connection Count, Msg Fragment Send Size Avg, Message Fragment Send Total, and so on.
You can use the Object Explorer in SQL Server Management Studio to view message types, contracts, queues, services, routes, and remote service binding details. This information is available under the Service Broker folder for each database in the Object Explorer tree.
You can use the catalog views and DMVs listed in Tables 15.1 and 15.2 to verify Service Broker configuration and execution. SQL Server writes Service Broker log and error messages to SQL Server error log files and the Windows event log. You can look in error log files and the event viewer for any errors that are interfering with Service Broker communication. For instance, if the database master key is missing, SQL Server logs the following message to the SQL Server error log:
Service Broker needs to access the master key in the database 'AdventureWorks'. Error code:25. The master key has to exist and the service master key encryption is required.
Here are some additional techniques you can use for troubleshooting Service Broker applications:
You can debug an activated stored procedure by writing PRINT statements in it. Service Broker writes the output of the PRINT statement into the SQL Server error log file (ERRORLOG).
If a stored procedure is not activated, you can use the sys.service_queues catalog view to review fields such as is_activation_enabled, activation_procedure, is_receive_enabled, execute_as_principal_id, and so on. You need to confirm that the security principal has EXECUTE permission on the stored procedure. You can review the SQL Server error log for any additional information.
If messages remain in the transmission queue (sys.transmission_queue), you can use the is_broker_enabled column of the sys.databases catalog view to check whether Service Broker is enabled for the database. You should also check the transmission_status column in the sys.transmission_queue catalog view to see error text that describes the last error that occurred while trying to deliver messages for a specific dialog. If messages are exchanged across instances, you need to ensure that Service Broker is enabled in the msdb system database and that routes are configured correctly in this database.