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://msdn.microsoft.com/en-us/library/ms166086.aspx
*email sending issue. Service broker is disabled, restore old version of msdb .
Vinay Thakur 