locked
SignalR stopped notifications after ALTER DATABASE ENABLE_BROKER was accidentally executed twice RRS feed

  • Question

  • User-1319896600 posted

    Hi,

    I set up a sample signalR ASP .net MVC application with SQL Server Service broker. It was working just fine. Each time, there was a change in the database (that was enabled with ENABLE_BROKER), the asp .net app would get notified. I used the tutorial at http://venkatbaggu.com/signalr-database-update-notifications-asp-net-mvc-usiing-sql-dependency/ and it worked just fine.

    By mistake, I then executed ALTER DATABASE DatabaseName SET ENABLE_BROKER ; for some reason, it kept executing and never completed. It ran for more than an hour and then I stopped this command from Query Analyzer.

    As a result of running the ALTER DATABASE, the asp .net app stopped getting notified. Is there a way to debug the broker service? I tried creating a new database and enabled it with broker service, but it never sends out notifications. It looks like there is something at the service level that is stuck and does not let notifications out. 

    When I execute sp_who2, I noticed a command that is always present and is in a SUSPENDED state, is this normal?

    Status             Command     ProgramName

    SUSPENDED    DELETE         SqlQueryNotificationService-612b3033-ec5e-41e9-9b60-253b0c79c2c8

    Thursday, July 2, 2015 7:31 PM

Answers

  • User-1129594362 posted

    First check in which tables the broker service are enabled, by using the following command

    SELECT name, is_broker_enabled FROM sys.databases

    Firstly the following command works fine if the database is not in use, Regarding problem consider an example that if you have opened two new query window editor for that database, In one of the query window you are executing the following command it will keeps executing the command until all the database related connection closed for that database

    ALTER DATABASE test SET ENABLE_BROKER

    So to avoid the above problem we need to add WITH ROLLBACK IMMEDIATE to the above query, the query will be as follows

     ALTER DATABASE  BlogDemos SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE 

    Another Fix Execute the following query

    ALTER DATABASE BlogDemos SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE BlogDemos SET ENABLE_BROKER
    ALTER DATABASE BlogDemos SET MULTI_USER

    The first command will set the SINGLE_USER permission on the database with ROLLBACK IMMEDIATE

    The Second command will enable the Broker Service
    The Third command will set the MULTI_USER permission on the database

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 3, 2015 2:16 AM

All replies

  • User-1129594362 posted

    First check in which tables the broker service are enabled, by using the following command

    SELECT name, is_broker_enabled FROM sys.databases

    Firstly the following command works fine if the database is not in use, Regarding problem consider an example that if you have opened two new query window editor for that database, In one of the query window you are executing the following command it will keeps executing the command until all the database related connection closed for that database

    ALTER DATABASE test SET ENABLE_BROKER

    So to avoid the above problem we need to add WITH ROLLBACK IMMEDIATE to the above query, the query will be as follows

     ALTER DATABASE  BlogDemos SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE 

    Another Fix Execute the following query

    ALTER DATABASE BlogDemos SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE BlogDemos SET ENABLE_BROKER
    ALTER DATABASE BlogDemos SET MULTI_USER

    The first command will set the SINGLE_USER permission on the database with ROLLBACK IMMEDIATE

    The Second command will enable the Broker Service
    The Third command will set the MULTI_USER permission on the database

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 3, 2015 2:16 AM
  • User-1319896600 posted

    Thanks, that worked.

    Monday, July 6, 2015 11:35 AM