Service Broker Operations and Troubleshooting


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

Catalog View

Description

sys.service_message_types

This catalog view contains one row per message type registered in the service broker.

sys.service_contracts

This catalog view contains one row for each contract in the database.

sys.services

This catalog view contains one row for each service in the database.

sys.service_broker_endpoints

This catalog view contains one row for the Service Broker endpoint.

sys.remote_service_bindings

This catalog view contains one row per remote service binding.

sys.transmission_queue

This catalog view contains one row for each message in the transmission queue.

sys.routes

This catalog views contains one row per route.

sys.service_queue_usages

This catalog view contains one row per (service, queue) pair.

sys.service_contract_message_usages

This catalog view contains one row per (contract, message type) pair.

sys.service_contract_usages

This catalog view contains one row per (service, contract) pair.

sys.conversation_groups

This catalog view contains one row for each conversation group.

sys.conversation_endpoints

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

DMV

Description

sys.dm_broker_activated_tasks

This DMV contains a row for each stored procedure activated by Service Broker.

sys.dm_broker_connections

This DMV contains a row for each Service Broker network connection.

sys.dm_broker_forwarded_messages

This DMV contains a row for each Service Broker message that the SQL Server instance is in the process of forwarding.

sys.dm_broker_queue_monitors

This DMV contains a row for each queue monitor in the instance. A queue monitor manages activation for a queue.


Tip

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

ID

Trace Event

Description

163

Broker:Activation

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.

138

Broker:Connection

Reports the status of a transport connection managed by Service Broker.

124

Broker:Conversation

Reports the progress of a Service Broker conversation.

136

Broker:Conversation Group

Occurs when Service Broker creates a new conversation group or drops an existing conversation group.

161

Broker:Corrupted Message

Occurs when Service Broker receives a corrupted message.

140

Broker:Forwarded Message Dropped

Occurs when Service Broker drops a message that was intended to be forwarded.

139

Broker:Forwarded Message Sent

Occurs when Service Broker forwards a message.

141

Broker:Message Classify

Occurs when Service Broker determines the routing for a message. A message is classified for local, remote, or delayed delivery.

160

Broker:Message Undeliverable

Occurs when Service Broker is unable to retain a received message that should have been delivered to a service in this instance.

143

Broker:Queue Disabled

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.

149

Broker:Remote Message Acknowledgement

Indicates when an acknowledgement has been received.

142

Broker:Transmission

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

Performance Object

Description

Broker Activation

Contains Performance Monitor counters that report information about stored procedure activation, such as Stored Procedures Invoked/sec, Tasks Running, and so on.

Broker Statistics

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.

Troubleshooting Tips

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.




Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net