SET NEW_BROKER statement hangs system

Question

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

Answer 1

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

Answer 2

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

 

Answer 3

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

 

 

 

Answer 4

ALTER DATABASE ... SET NEW_BROKER WITH ROLLBACK_IMMEDIATE;

Should last few seconds. Which CTP did you instaled ('published date')?

Answer 5

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

 

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

 

Answer 11

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

Answer 12

Jason,

did you try using the WITH ROLLBACK IMMEDIATE option as Remus have suggested in this thread?

Answer 13

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

Answer 14

Thanks, mine was hanging, but once I set the Database to Single User mode the query executed almost instantly. 

Jeremy High-Flying.co.uk