none
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

    Wednesday, February 07, 2007 7:13 AM

Answers

All replies

  • 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 07, 2007 7:54 AM
    Moderator
  • 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 07, 2007 8:31 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 07, 2007 11:32 AM
  • ALTER DATABASE ... SET NEW_BROKER WITH ROLLBACK_IMMEDIATE;

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

    Wednesday, February 07, 2007 4:20 PM
    Moderator
  • 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 08, 2007 5:21 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 08, 2007 8:06 AM
    Moderator
  • 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

    Thursday, February 08, 2007 9:15 AM
  • 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

    Thursday, February 08, 2007 5:01 PM
    Moderator
  • 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

    Friday, February 09, 2007 4:43 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

     

    Tuesday, October 30, 2007 11:40 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 09, 2009 4:57 AM
  • Jason,

    did you try using the WITH ROLLBACK IMMEDIATE option as Remus have suggested in this thread?
    Monday, March 09, 2009 6:54 PM
    Moderator
  • 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
    Monday, March 23, 2009 1:50 AM
  • Thanks, mine was hanging, but once I set the Database to Single User mode the query executed almost instantly. 

    Jeremy High-Flying.co.uk
    Friday, January 20, 2012 2:34 PM