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).
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
Last activity: Dec 15, 2023, 05:23 AM