troubleshooting Question

service broker calling stored procedure... queue stops workng and starts building up

Avatar of AidenA
AidenA asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
5 Comments1 Solution788 ViewsLast Modified:
Don't know much about service broker but we have an issue which occurs every month or so where the target queue starts building up and stops functioning completely, so it just continues to build up until we restart SQL Server and then all is well again.

I've been looking into this but haven't really enough knowledge of the issue to be able to properly trouble shoot it. Basically, if I look at 'select * from sys.transmission_queue' then this is empty which shows that the message is going to the target queue ok, which we can see.

I have about 10 mins of sql server profiler events with only the service broker events selected... the events I see are ConversationGroup, Conversation, Message Classify from .NET SqlClient Data Provider and then the same events from SQLAgent - TSQL Jobstep (job number : step 1) and DatabaseMail - DatabaseMail90 - ID<id>

and seems to repeat that process... can anyone explain that or where I can see if there is some error? I'm wondering about the TSQL - Jobstep... the EventSubClass goes like Create > Being Dialog > Send Message > Local > Dialog Created > Received Sequenced Message. Does that mean it worked ok? Although... that all seems to be a mail service of some kind?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros