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.


            @dialog = conversation_handle
            FROM <QUEUE>
        ), TIMEOUT 500 
        end conversation @dialog with cleanup

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

WHILE EXISTS (SELECT 1 from [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271])
        @dialog = conversation_handle
        FROM dbo. [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]
    ), TIMEOUT 500 

    end conversation @dialog with cleanup

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

  • Who is the drop being blocked by? You should be able to tell in sys.dm_exec_requests Commented May 21, 2018 at 21:44
  • I'll assume its the DROP SERVICE task. It appears to have a wait_type of null and a last wait type of PAGELATCH_EX... so, its not?
    – Chris Rice
    Commented May 21, 2018 at 21:53
  • What is the blocking_session_id for that row? THAT is the task you need to check on. Commented May 21, 2018 at 22:13
  • There isn't one. I feel there's some microsoft voodo going on here. I found a route for deleting most of them. I'll post that in a bit.
    – Chris Rice
    Commented May 21, 2018 at 22:16

1 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:


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.