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!
SQLZealots 