October 12, 2011 at 10:48 pm
Hi Folks,
Am trying to enable service broker to my database which is in SQL SERVER 2005 with Query:
Alter database [databasename] set enable_broker
this query is still executing from past 16hrs.
Can anyone tell me how much time takes to enable service broker?
TIA....
October 12, 2011 at 10:53 pm
that query needs to take a very brief exclusive lock on the database (like 1-2 seconds).
if others are currently using the database, your query will block indefinately. check who the query is blocking on, and get them to either disconnect, or change databases (9 times out of 10, I block myself with a second query window I had forgotten about. doh!)
To forceably kick people out:
ALTER DATABASE .. SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then enable broker and set it back to multi_user.
October 13, 2011 at 2:49 am
Thanks for the reply.
Issue solved. this happens because of deadlock on that process.
i've killed that spid and this query executed successfully.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply