Last post Jul 06, 2015 11:35 AM by ihaveaquestion
Jul 02, 2015 07:31 PM|ihaveaquestion|LINK
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
Jul 03, 2015 02:16 AM|madan535|LINK
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
Jul 06, 2015 11:35 AM|ihaveaquestion|LINK
Thanks, that worked.