Hi,

I set up a sample signalR ASP .net MVC application with SQL Server Service broker.  I used the tutorial at http://venkatbaggu.com/signalr-database-update-notifications-asp-net-mvc-usiing-sql-dependency/ and it worked just fine.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.

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

ALTER DATABASE...ENABLE_BROKER requires exclusive access to the database.  It will be blocked if there are any connections to the database, which is probably why it ran so long.  I wouldn't expect any database changes to have occurred due to cancelling it.

The DELETE command in SUSPENDED status is normal when a service broker RECEIVE is in progress.  This looks to be an active query notification session (or SqlDependency) based on the reported program name.  The sys.dm_qn_subscriptions DMV will show existing subscriptions.  Check the status as that might provide a clue as to your the cause of your issue.

For further troubleshooting, try a Profiler or Extended Event trace.  If this is a test server, you could try a "KILL QUERY NOTIFICATION SUBSCRIPTION ALL" command and then resubscribe, although that's a shot in the dark.

The sys.dm_qn_subscriptions DMV has no rows in it. I am using SqlDependency in my C# code file.

Upon checking the SQL Server error log, I see "The Service Broker endpoint is in disabled or stopped state" 

How do I enable the endpoint? which endpoint is it referring to? This might be the source of the problem.

I am using the following version

Microsoft SQL Server 2014 - 12.0.2000.8 (Intel X86) 
Feb 20 2014 19:20:46 
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.3 <X64> (Build 9600: ) (W

Are you perhaps using AlwaysOn? I dont' know if there's a resolution but there is a connect item about this error message and AlwaysOn:  https://connect.microsoft.com/SQLServer/feedback/details/783219/alwayson-the-service-broker-endpoint-is-in-disabled-or-stopped-state

It's probably not related as query notifications don't require SB endpoints.  SB endpoints are used for message routing to different instances, which I don't think applies here.

No, I am not usin