July 9, 2008 at 10:40 am
I set up a Service and a Queue to track all of the logging attempts on our DB, as described in this plan:
I shut down the stored procedure, thinking that would stop the entire procress from drawing in data, or at least keeping that data. However, the Queue still appears to have a HUGE amount of data in it. When I try to drop the queue, it says I can't delete due to associated services.
Any ideas on how to drop the service or otherwise get rid of all this extra information in the queue?
July 9, 2008 at 10:51 am
Try to Drop in this Order and post any errors.
DROP ROUTE route_name
DROP SERVICE [servicename] ;
DROP QUEUE QUEUENAME
Maninder
www.dbanation.com
July 9, 2008 at 11:02 am
Dropping the Route is fine, but dropping the service takes an incredible amount of time. Hours and hours, and its just hanging, although not causing any blocks.
July 9, 2008 at 11:16 am
July 10, 2008 at 2:20 pm
The solution for this was due to the conversations that had not completed in the queue. To resolve it, I ran the command END CONVERSATION [conversation_guid].
I was able to track down the conversation_handle (the guid) by select * from sys.endpoints.
Ending the conversation took a pretty huge amount of time, but considering there were 26million rows in there, I figured that was pretty acceptable.
July 10, 2008 at 9:56 pm
Service Broker tip: make sure your procedures are ending their conversations (both sides)!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply