Wednesday, May 02, 2007

 

Quick Tip: What to do if this statement hangs: ALTER DATABASE msdb SET ENABLE_BROKER

Ran across this 2x in the past week as I was working with new installations of SQL Server 2005 and I thought I'd post about it in case it helps someone else.

If you want to use Database Mail, Service Broker has to be enabled in the msdb database. The script to enable Service Broker is ALTER DATABASE msdb SET ENABLE_BROKER but, unless you have exclusive access to msdb, it will wait until you do before it runs. Here's how to see this in action:
  1. Start the SQL Server Agent
  2. In a query window, run ALTER DATABASE msdb SET ENABLE_BROKER
    1. Notice that you are waiting, waiting, waiting...
  3. Stop SQL Server Agent
    1. Now notice that your ALTER DATABASE statement has finished
Just something to watch out for!

AddThis Social Bookmark Button

# posted by Scott Whigham @ 1:45 PM
Comments:
As an alternate to shutting down SQL Server Agent, one can push everyone off MSDB, enable broker on MSDB, and re-enable access to MSDB.


alter DATABASE MSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER database msdb set ENABLE_BROKER
go

alter DATABASE MSDB SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

=================================

Thanks for the tips, would have been stuck for weeks without your tip.

Daniel Adeniji
MS SQL Server DBA
# posted by Blogger Daniel : 6:24 PM
 
Thanks for the advice. Helped me out of a bind.
# posted by Blogger Kevin : 11:30 AM
 
Post a Comment

Links to this post:

Create a Link



<< Home

This page is powered by Blogger. Isn't yours?