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.
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.