SQL Server Service Broker for the current database is not enabled, so query notifications are not supported. If you want to use notifications, enable Service Broker for this database

The problem encountered last night also knows that the notifications service relies on the underlying Service broker. I thought it was only possible to execute the following script to enable Service Broker for the database.

ALTER DATABASE DBNAME set Enable_broker

However, after execution, the script has been in the execution state, do not agree, just in the busy other things do not see the results of the operation, the results of this morning a look, incredibly run has not ended. Although it is performed on a production database, the database is only 30G in appearance, but it does not have to be executed for an evening and is not finished, but to terminate execution, using

SELECT is_broker_enabled from sys.databases WHERE name = ' DBNAME '

View is_broker-enabled as 0, Service Broker is still not enabled

After Google, in a foreign forum found a solution:

ALTER DATABASE SET  with ROLLBACK IMMEDIATE; ALTER DATABASE SET Enable_broker;

Executes the above 2 statements without any waiting, prompting the command to complete.

SELECT is_broker_enabled from sys.databases WHERE name = ' DBNAME '

View Is_broker-enabled to 1

The New_broker option, explained in SQL Server Books Online:

Each database contains a Service Broker identifier. The service_broker_guid column of the sys.databases catalog view displays the Service Broker identifier for each database in the instance. Service Broker routing uses Service Broker identifiers to guarantee that all messages for a session are passed to the same database. Therefore, the Service Broker identifier should be unique across all instances on the same network. Otherwise, the message may be misrepresented.

SQL Server generates a new Service Broker identifier for each new database. Because the identifier is new, SQL Server can safely activate Service Broker message delivery in the new database. Other databases on the network should not have the same Service Broker identifier.

New_broker. This option activates Service Broker message delivery and creates a new Service Broker identifier for the database. Because none of the existing sessions in the database use the new instance identifier, this option ends these sessions and returns an error.

ROLLBACK immediate will roll back the unfinished transaction immediately.

SQL Server Service Broker for the current database is not enabled, so query notifications are not supported. If you want to use notifications, enable Service Broker for this database