Day 4: Service Broker Disabled for MSDB

Description:-

SNTP related jobs which sends notification failed email failing with following error:

Message

Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery. [SQLSTATE 42000] (Error 14650).  The step failed.

 

Service broker is introduced in sql server 2005, and is useful to queue up and configuring database mail, be default when we install sql server 2005 service broker is ENABLED for MSDB database.

Observation:-

When checked on this server found that we could not able to access MSDB database as the owner was blank. So to correct it we have changed the owner of MSDB database to “sa” and there on we could able to access MSDB database. But database mail configuration is still failing with above error.

Resolution:-

>> After further investigation found that someone who wanted to copy all jobs related information restored MSDB database from earlier version (sql server 2000) to sql server 2005, as stated Service Broker is introduced in sql server 2005 hence Service Broker was disabled here

>> someone could also manually disabled Service Broker for MSDB database (I have not tried though)

to resolve the issue we need to Enabled service broker as

ALTER DATABASE [databasename] SET ENABLE_BROKERBut to run this command it requires Exclusive lock on the database means no one should use that database, but MSDB is used for sql server Agent processes. so we can use

>> Kill the agent related processes ( make sure its not so critical) it should be just monitoring and “Sleeping” state.

>> Safer side – restart the sql server agent if no job is running.

Reference  :

http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/e2c8501f-1a90-4921-a713-6e466bfa907a/

http://msdn.microsoft.com/en-us/library/ms166086.aspx

*email sending issue. Service broker is disabled, restore old version of msdb .

Advertisements
This entry was posted in Bug, Troubleshooting and tagged , . 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