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_queuehangs 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 <QUEUE>) BEGIN WAITFOR( RECEIVE TOP (1) @dialog = conversation_handle FROM <QUEUE> ), 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?
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