0

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.

    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

  • Who is the drop being blocked by? You should be able to tell in sys.dm_exec_requests – Aaron Bertrand May 21 '18 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 May 21 '18 at 21:53
  • What is the blocking_session_id for that row? THAT is the task you need to check on. – Aaron Bertrand May 21 '18 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 May 21 '18 at 22:16
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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