September 26, 2011 at 1:03 am
Hi All,
I found the below errror from the Eventviewer , My weekly jobs running with out any fail . Can you please help on this issue ::
Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdsb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker.
Thanks
Lavanya Sri
September 26, 2011 at 1:41 am
When did this happened?Any activity was done recently where the MSDB hosts(like MSDB restoration,files moved)?
Also check below status-
1)Check the SQL server agent status & also the DB engine.
2)check the SSAS status
3)what is the SQL server version and OS details?
4)when was the last DBCC CHECKDB ran on MSDB.
If every thing is ok then check the status of service broker ->
SELECT is_broker_enabled FROM sys.databases WHERE name ='MSDB'
(Note ->this is Final option-i,e if you didn't get anything what cause the issue you can do if the service broker not enabled you can enable via T-SQL
To enable
ALTER DATABASE [msdb] SET ENABLE_BROKER;
If you want to create a new broker, and thus a new GUID for the msdb database run the following script:
ALTER DATABASE [MSDB] SET NEW_BROKER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [MSDB] SET NEW_BROKER)
ALTER DATABASE [MSDB] SET ENABLE_BROKER
or
for temporary fix if every thing is fine
Manually stop the SQL Agent, then manually enable
Service Broker using a T-SQL command, then manually start up the Agent.
Best Regards,
Rama Udaya.K
September 26, 2011 at 3:07 am
rama38udaya (9/26/2011)
When did this happened? : i found this last week . [My All system databases are cloned from oter server]
Any activity was done recently where the MSDB hosts(like MSDB restoration,files moved)? :: no
Also check below status-
1)Check the SQL server agent status & also the DB engine. : Agent and DB engine is working fine
2)check the SSAS status :: NO SSAS
3)what is the SQL server version and OS details? :: SQL Server standard edition 64 bit sp3 , windows 2008
4)when was the last DBCC CHECKDB ran on MSDB. : 24/09
If every thing is ok then check the status of service broker ->
SELECT is_broker_enabled FROM sys.databases WHERE name ='MSDB' :
is zero
What is the impact of this if we r not enabled this ??
if we want to enable this , do we require any down time ??
Regards,
Lavanya sri
September 26, 2011 at 7:25 am
Service Broker ->it is an new feature in SQL server 2005(in sql server 2000 it is not there),Developers can take advantage of this new feature for Ex-
When performing DML activity like,inserting/updating/deleting records from a table if Application team want to call a trigger which writes the changed records in a tempTable or in any other table.
Also this new feature is capable of distributed messaging framework that allows for asynchronous programming support &the Service Broker is a new platform for building asynchronous , distributed database applications.With this new feature internal or external SQL Server instance(s) can send and receive guaranteed, asynchronous messages by using Transact T-SQL. Messages can be sent from within the same database, different database, or even remotely located SQL Server instances.
well suits for the multi-user web application, etc...
If you want to know more about that why the SBS(service broker service) and understand each parts of it.->see below link
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://technet.microsoft.com/en-us/library/ms345108%28SQL.90%29.aspx
If you no need about Service Broker functionality or any other functionality not dependent on it (Event Notifications, Query Notifications, SqlDependency, SQL database mail etc) then you can ignore the message.
(Mae sure you are well aware of all the service broker concepts and how it will work).
If you need Service Broker functionality, you should enable back the broker in MSDB:
ALTER DATABASE [msdb] SET ENABLE_BROKER;
Note-> If you are going enable Service Broker in any database requires a database lock. So it required to kill all the open connections to msdb.
-- step 1
USE master ;
GO
ALTER DATABASE msdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
-- step 2
USE master ;
GO
ALTER DATABASE msdb SET ENABLE_BROKER ;
GO
--step3
USE master ;
GO
ALTER DATABASE msdb SET MULTI_USER
GO
Best Regards,
Rama Udaya.K
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply