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 <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?
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
DROP SERVICE
task. It appears to have a wait_type of null and a last wait type ofPAGELATCH_EX
... so, its not?