Monday, October 04, 2010

SQL Service Broker Hangs while enabling

Hi,

This is one of the weird situations I faced while exploring SSB. As a naive I just bothered to find out "Service Broker" folder in the database (Am using SQL Server 2008 ). I struggled writing scripts for the Message, Contract, Queue, Service etc. Everything goes well but am not able to send messages to the queue.
After wasting almost half day googling, found out the service broker is disabled. This you can find out by right clicking on "Service Broker" => Reports => Standard Reports and "Statistics"

From my previous references I knew how to enable Service Broker

ALTER DATABASE data_base_name SET ENABLE_BROKER

It badly hangs up the system, that I expected of doing some work in the background. Finally got to know that this would happen if the database is multi user

So a crack(solution) to this is

ALTER DATABASE data_base_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE data_base_name SET ENABLE_BROKER
ALTER DATABASE data_base_name SET MULTI_USER

Wednesday, September 20, 2006

Hi Friends,

This is first time am going in for blogs. Here i will be posting Technical and Personal experiences to be shared.

Cheerio,
cadaats.