Enabling Service Broker Hangs


Summary:
Stop SQL Server Agent before you enable service broker.

One of our production database clusters has developed a nasty habit of losing network connectivity with no error messages or indicators of why, necessitating 1am drives to the NOC by a network admin to growl at it and scramblings to put the system it backs into emergency maintenance mode. The second time this happened, the operations team quite sensibly requested that I move all the databases to another server so that they could diagnose this one.

We had just transitioned our other production cluster from a set of servers attached to an MSA 1000 to a new set of servers attached to an EVA 8100, and the MSA and old servers were still hanging around. It was a matter of a few hours to coordinate offline time (thankfully, this database cluster housed non-24-hour-available systems), and agitate at developers until they changed connection strings and deployed code.

All went smoothly except that my job monitor — a job that runs every two hours and emails me about jobs which have failed or taken longer than usual in the meantime — failed. When it went to send email, it complained that service broker needed to be enabled for the database (no mention of which database, but I assumed msdb). I nipped over the BOL, found the appropriate command, and ran it. Twenty minutes later, it was still running. I killed it and started it again. Same hanging. I hopefully ran the command in the database that houses the job monitor sproc. It finished immediately, but it didn’t make mail work.

Eventually, it occurred to me that the SQL Server Agent, with its dirty little fingers constantly in msdb, could be causing a database configuration change to wait indefinitely for some sort of exclusive access. I stopped SQL Server Agent, ran the command to enable service broker, and it returned immediately. Yay!

Leave a Reply

Your email address will not be published. Required fields are marked *


*