Recently i was troublshooting a service broker application.
Here are the useful objects
sys.conversation_endpoints Catelog
is_broker_enabled field in sys.databases
Should be enabled
If disabled try following statements
ALTER DATABASE my_database SET SINGLE_USER
ALTER DATABASE my_database SET ENABLE_BROKER
ALTER DATABASE my_database SET MULTI_USER
(It is very strang after a abnormal shutdown of SQL Server, database goes to recovery mode. When it come back the is_broker_enabled was disabled. It took 3 hrs. to identify the problem :(
)
