SQL Service Broker suddenly stops working

Question

Post reply

SQL Service Broker suddenly stops working

eamasalunga

Old Hand

Points: 329
More actions
May 16, 2013 at 11:40 pm

#293368

Hi,
I'm new in using service broker. Our existing service broker suddenly stops working and I can't figure out why.
When I try to enable service broker (which is already enabled) I get this in SQL error log:
An error occurred in the service broker manager, Error: 3602, State: 25.
Also tried creating new message queues. I am able to see the created queues under sys.service_queues and sys.objects but not in sys.dm_broker_queue_monitors.

Viewing 6 posts - 1 through 5 (of 5 total)

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

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 1

Have you tried Stopping and starting the queue..?

here link

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

eamasalunga Old Hand Points: 329 More actions · Answer 2

Hi,

Yes I've tried disabling and re enabling the queue, restored the database, restarted the sql service and even restarted the server itself but still getting the said error.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 3

eamasalunga (5/17/2013)
Hi,
Yes I've tried disabling and re enabling the queue, restored the database, restarted the sql service and even restarted the server itself but still getting the said error.

Please check this link[/url] might be helpful

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

eamasalunga Old Hand Points: 329 More actions · Answer 4

Hi all,

I've recently encountered this error again today after server restart and I was able to fix it.

What I did was open/modify the message queue and execute the ALTER QUEUE command again and voila, the queue re appears on the sys.dm_broker_queue_monitors and messages are being processed again. Now what I need to know is why do I need to do this in order to get it functioning again. 🙂

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 5

eamasalunga (5/20/2013)
Hi all,
I've recently encountered this error again today after server restart and I was able to fix it.
What I did was open/modify the message queue and execute the ALTER QUEUE command again and voila, the queue re appears on the sys.dm_broker_queue_monitors and messages are being processed again. Now what I need to know is why do I need to do this in order to get it functioning again. 🙂

Many possible reasons a Service Broker queue gets hung.

1) Detach/Attach disables Trustworthy/Service Broker at the database level.

2) Poison message handling - 5 exceptions and it closes the queue

How do you diagnose? First, check the event log and see what is there. Second, Check for error logging tables to see if you missed anything in custom logging (you do have those right?). Third, select the top 10 from the queue and see if anything is amiss.

There are likely a lot more avenues to trundle down for diagnosing your specific issues, but this is a good starting point for troubleshooting.