Service Broker enabled by default while creating a database through T-SQL in SQL Server

Recently, we decided to disable Service Broker for all databases as part of our deployment. While doing a preliminary check, we observed that when we create a database through T-SQL, by default, the database is enabled with Service Broker.

Please find the below code to replicate the case.

Script:

IF NOT EXISTS(SELECT * FROM sys.databases WHERE [name] = 'testdb')
BEGIN
CREATE DATABASE testdb
Select 'Database created...'
END
GO
select is_broker_enabled,name from sys.databases where name = 'testdb'
GO
Drop database if exists  testdb
Select 'Database dropped...'

screenshot:

We also observed that while creating a database through SSMS interface/wizard, the service broker is disabled by default. Out of curiosity, SQL profiler has been set up, captured the statements and identified that when we create a database through SSMS interface, there is an explicit execution of disabling the Service Broker is happening followed by create database statement as below in the screenshot.

Conclusion:

Always DISABLE service broker explicitly ONLY if you do not really need it to be enabled on your database. Please note, there is no overhead when service broker is enabled but not used. On the flip side, disabling service broker can prevent the applications from using the feature of queuing and usage of components like SqlDependency/QueryNotifications that rely on it. Please never try to turn off service broker on your system databases like msdb as it can cause break in functionalities like database mail etc.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s