SET NEW_BROKER statement hangs system
Question
Wednesday, February 7, 2007 7:13 AM
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
All replies (14)
Thursday, February 8, 2007 5:01 PM âś…Answered | 1 vote
I had a typo, there is no '_' between rollback and immediate. The correct syntax is in BOL: http://msdn2.microsoft.com/en-US/library/ms174269.aspx
ALTER DATABASE GPx SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
HTH,
~ Remus
Wednesday, February 7, 2007 7:54 AM
One way is to upgrade to the latest CTP of SP2 (which has this issue fixed).
Alternatively, end conversations with CLEANUP using a cursor over sys.conversation_endpoints and commit in batches of 1000 dialogs in one transaction. It should last about 20-30 minutes to clean them all out. After that run the NEW_BROKER again.
HTH,
~ Remus
Wednesday, February 7, 2007 8:31 AM
thanks ... Remus Rusanu .. since it is a test server i am going to install SP2 CTP . thanks again
i found a thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1
apart from this is there any other solution.
Madhu
Wednesday, February 7, 2007 11:32 AM
I have installed SP2 CTP and ran
Alter database somedatabase SET NEW_BROKER
this statement is running for last 3 hours. I am not sure is this bug fixed in SP2.
Madhu
Wednesday, February 7, 2007 4:20 PM
ALTER DATABASE ... SET NEW_BROKER WITH ROLLBACK_IMMEDIATE;
Should last few seconds. Which CTP did you instaled ('published date')?
Thursday, February 8, 2007 5:21 AM
thanks for u r valuable time
Result of @@Version is this:-
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)
(1 row(s) affected)
I downloaded SP CTP on 5 Dec 06.
Madhu
Thursday, February 8, 2007 8:06 AM
There is a later CTP, the 19th of December one: http://www.microsoft.com/downloads/details.aspx?FamilyId=D2DA6579-D49C-4B25-8F8A-79D14145500D&displaylang=en
The SQL Server version should be 9.00.3033 or higher.
HTH,
~ Remus
Thursday, February 8, 2007 9:15 AM
I am installed latest SP2 CTP as u said. now my @@Version result is
Microsoft SQL Server 2005 - 9.00.3033.00 (Intel X86)
Dec 6 2006 17:53:12
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 row(s) affected)
Then i ran
ALTER DATABASE GPx SET NEW_BROKER WITH ROLLBACK_IMMEDIATE
Result of above statement
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ROLLBACK_IMMEDIATE'.
waiting for u r guidance.... thanks again for the time...
Madhu
Friday, February 9, 2007 4:43 AM
Thanks Remus..... thanks a lot... It worked like a magic.... it is wonderful to know that MS has addressed this issue in SP2.. Thanks again for your valuable time.
Madhu
Tuesday, October 30, 2007 11:40 AM
Hi,
I found the following queries very useful from the post.
1) ALTER DATABASE xDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
2) Alter database xDB SET NEW_BROKER
3) ALTER DATABASE xDB SET ENABLE_BROKER;
The problem I am facing is, I created a New Broker. Till second query everything was workinf fine and it got executed in seconds. When I ran the second query, It took hours and did enable the broker service on my DB.
One more thread, I am using the SQL Service Broker to implement the Page Cache concept in which I have a Gridview and wants to update the data on updation in DB. To achive this I have add the attrivute in my page;
<%@ OutputCache Duration="20" VaryByParam="*" SqlDependency="CommandNotification" %>
and even added a line of code in my Global.asx file
System.Data.SqlClient.SqlDependency.Start(SABIC.Common.CommonFiles.cConfiguration.ConnectionString);
When I run the application it gives error saying SQL Service is not enabled.
Kindly suggest what needs to be done to resolve the problem. And even provide information what are the uses of enabling SQL Service Broker, what are the other services that needs to be enabled the output I am looking at
Monday, March 9, 2009 4:57 AM
I'm having the exact same problem as the original poster was:
ALTER DATABASE x SET NEW_BROKER
Has hung the system for over 2 hours now. The thing is, I'm using a much newer version of SQL Server 2005 than the one referenced above. Using SP2, version 9.00.3077.00, Express Edition.
What's going on? ENABLE_BROKER and DISABLE_BROKER are both hanging as well. I restored a database from a backup, and have not found a way to get the service broker running again. Sys.databases shows that the service broker is not enabled for the database in question.
Thanks!
Jason
Monday, March 9, 2009 6:54 PM
Jason,
did you try using the WITH ROLLBACK IMMEDIATE option as Remus have suggested in this thread?
Monday, March 23, 2009 1:50 AM
To set the Broker Option you need to make sure that your Db is in Single User Mode. I was facing same issue while nearly 120 users were connectd to my DB. I managed to notify the users requesting to close theor connections..I took the Downtime granted ..took to DB to Single user and it wa s fine. Took the DB back to normal.
Hope this helps :)
Anant
Friday, January 20, 2012 2:34 PM
Thanks, mine was hanging, but once I set the Database to Single User mode the query executed almost instantly.
Jeremy High-Flying.co.uk