Sample Header Ad - 728x90

Drop service hangs indefinitely

2 votes
1 answer
2855 views
I have a mirrored database that was using a service broker to push notifications to a client. The broker creation framework started making duplicates and this caused serious problems so i rolled back to a different framework. The problem is i cannot cleanup the the existing services that are not longer in use (or should be). enter image description here This line hangs indefinitely. DROP SERVICE [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] * I've tried stopping the broker but i cannot because it is a mirrored database. * I've tried killing every single transaction in the database to free the lock but that didn't work. * I've tried working my way up by disabling the queue and then deleting the contracts and message types but i cannot while the service still exists. * I was able to disable the queues and delete the activation stored procedures but this didn't seem to do anything. * Most of the queues do appear to have some rows in them. * SELECT * FROM sys.transmission_queue hangs indefinitely as well. * EDIT: i was able to empty the queue with the following, but it didn't actually fix the issue. DECLARE @dialog UNIQUEIDENTIFIER WHILE EXISTS (SELECT 1 from ) BEGIN WAITFOR( RECEIVE TOP (1) @dialog = conversation_handle FROM ), TIMEOUT 500 end conversation @dialog with cleanup end I'm afraid i'm going to have to break mirroring in order to make a new broker. This is a live production system and i'd rather not do that.. Anything else i can try? **BIG EDIT** So i was able to finally figure out a way to delete ***most*** of them. I first issued this to empty out the queue -- Create variables used to hold information DECLARE @dialog UNIQUEIDENTIFIER WHILE EXISTS (SELECT 1 from [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]) BEGIN WAITFOR( RECEIVE TOP (1) @dialog = conversation_handle FROM dbo. [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] ), TIMEOUT 500 end conversation @dialog with cleanup end Then i altered the queue with ACTIVATION ( DROP ) ALTER QUEUE [dbo]. [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( DROP ), POISON_MESSAGE_HANDLING (STATUS = OFF) After this i was able to drop the service and then the queue. It only seems to take 3-4 minutes per queue to delete. DROP service [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] DROP QUEUE [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] At this point all but 2 of the 15 services that refused to drop to have been deleted after stopping and retrying over and over again. **There obviously is something blocking here but as discussed in the comments there isn't anything blocking when i run SELECT * FROM sys.dm_exec_requests**
Asked by Chris Rice (383 rep)
May 21, 2018, 09:19 PM
Last activity: Dec 15, 2023, 05:23 AM