Drop service hangs indefinitely

Question

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

Answer 1

Unfortunately i had to go the route i didn't want to. I had to break mirroring for the database and then create a new broker using:

ALTER DATABASE <db> SET NEW_BROKER WITH ROLLBACK IMMEDIATE

Once this completed i was able to instantly delete the services that were stuck.

My attempts to drop the services without setting the new broker actually completely broke my mirroring anyways so i was kinda forced into this method.