December 23, 2007 at 9:48 am
Hi guys,
im brazilian, and sorry for my bad english.
I have Sql Server 2005 Sp2, and when i try run the query "alter database xxx set enable_broker", the query never finish. I think that the query is "infinite loop"
anyboby had this problem?
December 23, 2007 at 2:23 pm
Something much be blocking it. Stop the query and run it again.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 23, 2007 at 6:01 pm
I try stop the query and run again and stop the sql service.
I don´ know what happent.
December 23, 2007 at 6:37 pm
Did you restart the service?
You should be able to start the service and run that query. It doesn't do much to the database and should complete quickly.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 24, 2007 at 12:33 am
- with sp2 at least implement up to Cumulative Update 2 to fix the first issues with sp2.
We have installed Cumulative update package 4 for sp2.
- I also advise to install MS performanceDashboard. (free download) It provides nice and usable reports for the dba.
- are there any other users on the db? (sp_who)
Maybe switch the db to single user before you active SSB.
/*begin*/
Alter database xxx set single_user with rollback immediate; -- others are thrown out !!
Alter database xxx set enable_broker;
Alter database xxx set multi_user ;
go
/*end*/
If you are trying to enable ssb for a restored db, maybe you should also generate a new SSB id for it !
/*
* Set New_broker was dragging slow but has been fixed with SQL2005 SP2
* Keep in mind to put the "rollback immediate" so it just truncates the table
* in stead of ending all conversations !
*/
ALTER DATABASE yourdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 24, 2007 at 8:42 am
Man..
Thanks for your attention !
ALZDBA (12/24/2007)
- with sp2 at least implement up to Cumulative Update 2 to fix the first issues with sp2.We have installed Cumulative update package 4 for sp2.
No, i only install sp2. I can´ find cumulative packs. If you have the site, please post.
- I also advise to install MS performanceDashboard. (free download) It provides nice and usable reports for the dba.
thanks man, i will find and download.
- are there any other users on the db? (sp_who)
Maybe switch the db to single user before you active SSB.
Yes, i think that had 5 users. But i did this same query in other costumer (with 200 users). I will try switch the db to single use
/*begin*/
Alter database xxx set single_user with rollback immediate; -- others are thrown out !!
Alter database xxx set enable_broker;
Alter database xxx set multi_user ;
go
/*end*/
If you are trying to enable ssb for a restored db, maybe you should also generate a new SSB id for it !
/*
* Set New_broker was dragging slow but has been fixed with SQL2005 SP2
* Keep in mind to put the "rollback immediate" so it just truncates the table
* in stead of ending all conversations !
*/
ALTER DATABASE yourdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
December 25, 2007 at 1:27 am
web link for service pack related:
December 25, 2007 at 6:25 am
"Enabling SQL Server Service Broker in any database require a database lock."
If someone is working on the database or your application is accesing it, this operation will go into an infinite loop.Set the database in the single user mode as was suggested in the previous reply and run the query once more.It should work.
December 26, 2007 at 2:32 pm
Hi guys,
Today i changed my database to "single_user" and i did it to "Enable_broker"
Thanks A LOT guys
Sorry for my english
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply