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;