Error: 8355, Severity: 16, State 1

Question

Error: 8355, Severity: 16, State 1

Roy Ernest

SSC-Dedicated

Points: 38780
More actions
October 15, 2010 at 6:56 am

#230520

Hi All,
Our main server is set up with two external drive shelves. Yesterday we lost one of our external drives where the data files of MSDB and our Application DBs is located due to a controller problem. We replaced the controller and we got the drive back.
When the server came up, it gave the message that MSDB was corrupted. Therefore I restored the MSDB with the latest copy (nightly Backup). All our database came up. I ran DBCC CheckDB with no_infomsgs. It ran clean with no errors reported.
But in the error log it gives Error: 8355, Severity: 16, State 1. This is due to the service broker not being enabled. I ran the query
select name,is_broker_enabled from sys.databases. I see that both MSDB and the Master DB has the Service broker disabled. Could someone tell me if I Alter the DB and enable the broker, everything will be OK? Should I enable it for Master as well? I did not do anything with the Master. So I am not sure if I should enable it or not.
-Roy

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 1

Having never worked with Service Broker, I couldn't tell you if it'll work just to re-enable it. But if you had this many problems with MSDB, maybe you should consider restoring Master. This way you're sure that nothing has gotten lost.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

Thanks Brandie. I will have to arrange for a shutdown time. We are 24/7 shop and it is always busy. We always have around 4000 batch request per second.

-Roy

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I'm not where I can check an existing system, however, iirc Database Mail uses Service Broker. If you are running Database Mail this may be why you are seeing this error. I don't see any reason you shouldn't try reenabling Service Broker on msdb.

Also, iirc, you should be able to do this without changing the SB id.

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 4

Lynn Pettis (10/15/2010)
I'm not where I can check an existing system, however, iirc Database Mail uses Service Broker.

Good call, Lynn! I didn't even think about that.

Books Online (subject: Database Mail)
When you call sp_send_dbmail to send a message, Database Mail adds a request to a Service Broker queue.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

We do not use DB mail. I will try to enable Service broker on MSDB. The question I have is should we enable Service broker in the Master as well.

-Roy

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 6

If I'm correct, any restore database command will disable SSB for the restored db.

It is up to you to double check if the SSB guid for the db is still ok for ssb to work properly.

My guess for msdb is, since it came from the actual db backup, and is restored on the same instance, just enabling it should be ok.

If you aren't using user defined SSB, it shouldn't hurt trying to enable it.

You will need it for dbmail, if in use.

btw error 8355 states: 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.

have a look at http://msdn.microsoft.com/en-us/library/ms166057.aspx

It contains a remark you must stop SQLAgent to be able to enable SSB for msdb !

Plan this stop/start of SQLAgent if needed !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

Thanks ALZDBA. I will have to plan for the agent shutdown and enabling it. I think we can live with it this weekend. (We are very busy during weekend)

Thanks everyone for the help. Since we do not use DB Mail or use Service broker for anything, we will just have to wait till the weekend is over.

-Roy

ALTAIR SOUZA SSC Rookie Points: 38 More actions · Answer 8

You'll have to set a new broker, after sqlagent is stoped, run:

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

In sys.databases column is_broker_enabled will be set to 1 and a new value will be set for service_broker_guid column, after restarting sql server the error will be no longer showed

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

ALTAIR SOUZA (10/6/2016)
You'll have to set a new broker, after sqlagent is stoped, run:
ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
In sys.databases column is_broker_enabled will be set to 1 and a new value will be set for service_broker_guid column, after restarting sql server the error will be no longer showed

Not sure anyone on the original thread is listening after 6 years but good to know. Thanks for posting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)