Problem

AdamsWeb Shows the below error.

Server Error in '/AdamsWeb' Application.
The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[InvalidOperationException: The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications.]
   System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +14764695
   System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +403
   SqlDependencyProcessDispatcher.StartWithDefault(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& service, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart) +0
   System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults) +1407
   Foray.Adams.DatabaseManager.RegisterForNotification(String alertName, String msSqlNotificationQuery, EventHandler eventHandler) +223
   Foray.Adams.AdamsWeb.Global.Application_Start(Object sender, EventArgs e) +2122

[HttpException (0x80004005): The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications.]
   System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +546
   System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +171
   System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +173
   System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +255
   System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +347

[HttpException (0x80004005): The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +552
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +122
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +737

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.8.4718.0

Solution

Run the following commands in SQL Sever Manager.

Verify if Service Broker is enabled:

SELECT name, is_broker_enabled
FROM sys.databases
WHERE name = 'YourDatabaseName';

If is_broker_enabled is 0, it’s disabled and you need to continue to the following steps.

Force single-user mode (to drop other connections):

ALTER DATABASE [YourDatabaseName]
  SET SINGLE_USER
  WITH ROLLBACK IMMEDIATE;

Enable the broker:

ALTER DATABASE [YourDatabaseName]
  SET ENABLE_BROKER;

Return to multi-user mode:

ALTER DATABASE [YourDatabaseName]
  SET MULTI_USER;

Re-verify:

SELECT name, is_broker_enabled
FROM sys.databases
WHERE name = 'YourDatabaseName';

You should now see is_broker_enabled = 1.

SQL Server Service Broker Not Enabled

Problem

Solution

Verify if Service Broker is enabled: