Archive | Service Broker RSS for this section

Short Tip of the Day!

I recently came upon a problem and it may benefit someone else so I am going to throw this one out there.

When auditing your database infrastructure, please make a note of if the service broker is enabled anywhere!

You ask, why?

Well, when restoring a SQL Server Database, it does not re-enable the Service Broker for that database if it was previously enabled!

So how do you determine if it is enabled?

SELECT is_broker_enabled FROM sys.databases WHERE name = 'DatabaseName'

One of the key things to keep in mind is that if you are turning it on, it will try to wait for all transactions to finish.  The best way to get around this is to set the database to single user mode, enable the broker, and then set it back to multi-user.

ALTER DATABASE DatabaseName SET single_user with rollback immediate  then run:

ALTER DATABASE DatabaseName SET new_broker  and then run:

ALTER DATABASE DatabaseName SET multi_user
So in short, before you run into issues down the line — make sure you know what databases are using the service broker and put in the appropriate notes for any restores that are to happen for that database!
Comments?  Drop a message!