ServiceBrokerEnable.sql ----------------------- http://www.davewentzel.com/content/service-broker-setup-routine. Setting up Service Broker can be daunting for a SB noob. This script handles a lot of the confusion, especially around when to ENABLE_BROKER vs NEW_BROKER. ServiceBrokerMonitor.sql-------------------------http://www.davewentzel.com/content/service-broker-monitoring-routine. For a noob, SB can be daunting. Especially when debugging. This script will help a bit, showing you the current status of your SB infrastructure. This will tell you: - if your db is_broker_enabled - looks for dropped queue monitors - queues sitting in the NOTIFIED state for long periods of time. (means that the Q activator is firing, but proper RECEIVEs are not occurring) - activated queues that are disabled (could mean the activated proc is throwing errors). - poison message detection - Conversation Population Explosion...conversations may not be properly moving to the CLOSED state. This usually happens when the target is not ending the conversation properly. - display the contents of queues for visual inspection - display various PerfMon metrics such as Activation Errors Total and Task Limit Reached. - display the last hour of SQL Server error logs, looking for activator procedure errors and PRINT statements - runs ssbdiagnose against your queues - there is a section to place custom TRACER TOKEN code. You need to code this. This would be similar to replication's tracer token feature. Essentially you code a "test" message that flows through your workflow and you monitor it to see if it runs to completion and if not where it failed. - has an option for STALLED MESSAGE CLEANOUT. This removes old conversations that you deem as not needed that are not in the CLOSED state. This is useful in dev envs where you changed some activator code and the queues failed and there are a bunch of useless messages. Probably not good to run this on a production db.
SSB_SHELL_Configure.sql------------------------http://www.davewentzel.com/content/setup-and-configure-service-broker-objects-reliably-and-repeatablyThis is a "pattern" I use for each new SSB "module" that I need to deploy. It helps ensure everything is reliably and consistently configured and easy to debug.