How to enable SQL Server Service Broker

31 May,2018 by Jack Vamvas

1) SQL Server Service Broker is enabled per database , so  If you need to enable Service Broker on multiple databases , some sort of dynamic level sql is required , as there is no Server level command to activate all databases .

2) When enabling Service Broker on a  database consider that if you recover and restore from a copy that does not have Service Broker enabled you'll need to have a process to enable Service Broker.

3) Attempt to complete the enabling of Service Broker at a non-critical time. Service Broker Activation requires an exclusive lock on the database - so all transactions are terminated , and all transactions which have not commited will roll-back.

4)If you need to activate a database automatically upon CREATE DATABASE , you have an option to enable service broker on the model database. That way , every time the CREATE DATABASE statement is used , the newly created database will include service broker enabled

5)Once you've enabled Service Broker , you may need to investigate issues. A good starting point - to give you some insight into Service Broker and service broker related logs use Troubleshooting SQL Server Service Broker

Check to see if Broker Service is enabled on a database

 

SELECT     name, database_id, is_broker_enabled FROM sys.databases

 

Enable Service Broker on a SQL Server database

 

ALTER DATABASE [DB NAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to enable SQL Server Service Broker


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer