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. ]

Source: Internet
Author: User
Tags sql server books sessions

http://blog.csdn.net/mss359681091/article/details/51726823

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 DatabaseName SET new_broker with ROLLBACK IMMEDIATE;
ALTER DATABASE Databasename 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. ]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.