Snowball - The Blog - SET ENABLE

Dear visitor, thanks for stopping by! If you want, you can follow all updates on Snowball.be via RSS. You can also follow me on Twitter or Facebook. More interesting posts from other Microsoft Regional Directors can be found at The Region.

SET ENABLE_BROKER hangs

Gill Cleeren

Efficiency | SQL Server

April 19, 2009

I’m preparing some demo’s on ASP.NET caching. One of them includes SQL Server Cache Notifications (with SQL Server 2005).

For this to work, the first thing you need to do is enabling the Service Broker on the database. This can be done using the SET ENABLE_BROKER command.

   1: ALTER DATABASE Northwind SET ENABLE_BROKER

This command seemed to be taking way too much time (I left it running several minutes, but it still didn’t do anything). I then stopped the database service and retried. The command completed in just seconds.

The reason appears to be the following: this command wants to create an exclusive lock on the database. However, if you still have open connections, it cannot create this lock and it thus waits and waits… and hangs.

Stopping the service closes all connections and makes it possible to create the broker.

Posted on: Sunday, April 19, 2009 12:06:11 PM (Romance Daylight Time, UTC+02:00) | Comments [2]

Monday, April 20, 2009 10:30:17 AM (Romance Daylight Time, UTC+02:00)

Hi Gil,

Indeed, enabling Service Broker requires an exclusive lock on the database. However there is no need to reboot the SQL Server. Just kick out everybody who's is connected to the database:

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

Have fun, with Service Broker !!

Frederik

Frederik Vandeputte

Monday, April 20, 2009 10:39:15 AM (Romance Daylight Time, UTC+02:00)

That's why we need our collegues from the SQLUG :)

gill

Comments are closed.

2/8/2012 9:38:49 PM

Welcome to Snowball.be

Hello and welcome to snowball.be!

My name is Gill Cleeren, I'm a Microsoft Regional Director and an MVP ASP.NET.
On Snowball.be, you'll find all kind news and articles on .net, ASP.NET, WPF, Silverlight and Microsoft in general.
More on me can be found on my about page.

Should you have any questions, don't hesitate to contact me by