SQL Server Performance Forum – Threads Archive

SET NEW_BROKER statement hangs system

SQL Version – SQL Server 2005 Standard Edition, SP 1 I have a database which is Broker Enabled. We use Query notification extensively and the application is in testing stage. Both Testing and development database is on the same server. When I create database for testing I used to do Backup-restore method. When i restore a borker enabled database, the restored database and the source database will have same GUID for Broker. So by default the restored database will not be broker enabled. So what I do is , i run Alter database somedatabase SET NEW_BROKER. This statement will generate new GUID for this database and then we can enable the Broker by SET ENABLE_BROKER statement. Till last week this process was working fine. When I did the same thing yesterday the Alter database somedatabase SET NEW_BROKER query was runing for whole night and it could not complete the process.
What SET NEW_BROKER internally does as per BOL is NEW_BROKER
Specifies that the database should receive a new broker identifier. Because the database is considered to be a new service broker, all existing conversations in the database are immediately removed without producing end dialog messages.
When I checked the Conversation in the database there were millions of rows.
My question is , can I do the same process in a better way and how. Is there any way to clear Conversation in a faster method. I googled , but I could not find a better solution.
pse help Madhu

i found a thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1 is this the only way to address this problem? Madhu

check this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=687834&SiteID=1 —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

ya….. i forgot to mention that i always do all these ativities in single user mode… Madhu

As mentioned in thread<a target="_blank" href=http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1>http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1</a> If you want to speed it up, you’ll need to work in parallel ! Having your db in single user mode, this cannot be done. I’d suggest: - you remove all members of the db_owner dbrole - perform your end-conversations in parallel - alter db yourdb set new_broker - and then add the needed db_owner members. als always, good preparation makes kooking easy. <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />

What i am planing to do is install SP2 CTP in another machine, restore the database there and try to run the SET NEW_BROKER , if this problem is addressed in SP2 CTP as mentioned in the thread this will not take much time i guess. Then i can do detach-atach method. i hope this is not a bad idea… Madhu

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by alzdba As mentioned in thread<a target="_blank" href=http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1>http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1</a> If you want to speed it up, you’ll need to work in parallel ! Having your db in single user mode, this cannot be done. I’d suggest: - you remove all members of the db_owner dbrole - perform your end-conversations in parallel - alter db yourdb set new_broker - and then add the needed db_owner members. als always, good preparation makes kooking easy. <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> <hr height="1" noshade id="quote"></blockquote id="quote"> I have installed SP2 CTP in my machine and i restored the database. then i ran Alter database somedatabase SET NEW_BROKER statement. it is executing for last 3 hrs. I think what this link mentioned is wrong. <a target="_blank" href=http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1>http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1</a>). result of @@Version after installing SP2 CTP is as follows :- Microsoft SQL Server 2005 – 9.00.3027.00 (Intel X86) Oct 27 2006 15:59:00 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2) i believe, i should report this to Microsoft

the link mentions : We split it up by queue name and ran 10 queries in parallel to end the conversations a little quicker. That’s what I refered to when talking of doing stuff in parallel to speed things up. So the alter database set new_broker will still be dragging slow because it will not execute the end-conversation and cleanup in parallel, but running the end-conversation in parallel yourself and then alter the db using alter database set new_broker will be faster. I guess you also be better off altering the routes before generating the end conversations.

my apprehension is that, is this problem really addressed in SP2 CTP. the above mentioned link says that in SP2 CTP this alter statement will just trucate the table. I have checked the list of bugs fixed in SP2 CTP inhttp://support.microsoft.com/default.aspx/kb/921896, but this problem is not mentioned there. From where i will confirm this. Madhu

the problem sovled and this link would help someone…. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1207910&SiteID=1&mode=1
alzdba…thanks for all the help Madhu

So it is the "rollback immediate" part that speeded it up with the SP2ctp ?

exactly… it just took few secs to clear more than 6 millions rows Madhu

Thank you for the feedback

]]>