Service Broker

While checking a SQL Server upgrade I came across a corrupt msdb database. Like a good DBA I had backed up all user databases along with msdb, master and model before the upgrade so I quickly restored msdb from the morning’s backup.

What I missed though was that restoring msdb reverted it to the default of having service broker disabled, which meant that database mail wouldn’t work. I didn’t catch this until the server, a production box, was brought back online.

If you don’t already know: database mail requires service broker to be enabled on msdb. Enabling service broker on msdb requires the SQL Agent to be stopped.

This was a lesson learnt hard – normally stopping the SQL Agent is not a huge deal, and the service broker enable is very fast, but in this case the SQL server was sitting on a cluster and there is always the chance that an agent outage will cause a cluster failover.

I had to get the client to schedule a maintenance window out of hours to complete the change. Needless to say I did not bill for that change. But here for your viewing pleasure are the relevant T-SQL commands for dealing with service broker:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name';
-- Where 'Database name' is the name of the database you want to query.
-- 1 is enabled.

-- Enable Service Broker:
ALTER DATABASE [Database Name] SET ENABLE_BROKER;

-- Disable Service Broker:
ALTER DATABASE [Database Name] SET DISABLE_BROKER;
Advertisements
This entry was posted in Database options, Service Broker, SQL Server, T-SQL. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s