2

I have Service Broker queue infrastructure that I am using in my database for a few months. I realized that my Initiator queue has reached to 2 million records which are EndDialog messages. So I re-designed it thanks to this link.

My problem is that I couldn't delete those 2 million records. I used the approach below as indicated in this link, and left the query executing. It executed for 20 hours until I canceled it. 

declare @c uniqueidentifier
while(1=1)
begin
    select top 1 @c = conversation_handle from dbo.queuename
    if (@@ROWCOUNT = 0)
    break
    end conversation @c with cleanup
end

Now I am trying to drop Service and Queue but it seems it is gonna take lots of time again. 

drop service initiatorService
drop queue initiatorQueue

Is there another way to delete immediately?

Queue Data; enter image description here

ibubi
  • 2,469
  • 3
  • 29
  • 50
  • have you got an example of your data please. It doesnt have to be real, but a couple of lines of data with the condition that youre looking to remove will be great. At the moment I can see why it would take some time because the code, on a while loop is not efficient and is doing a one by one taking up SQL Resource and you will probably find if you look at the event monitor that your job is suspended – Simon Price Oct 03 '17 at 06:14
  • @CodeWarrior I guess drop service is succesfully deleting messages code behind. After one hour now I saw that half of the messages has been deleted. The first code block, which uses `end conversation` is useless for my condition, I should use `waitfor receive..` I guess, I am not sure. I added the sample data. – ibubi Oct 03 '17 at 06:30
  • have you tried a simple `delete from [table name] where message_type_name = 'http://schemas.microsoft.com/sql.servicebroker/enddialog'` and then any other condition that you want to meet? – Simon Price Oct 03 '17 at 06:38
  • unfortunately, delete statements are not working on service broker queues. – ibubi Oct 03 '17 at 07:09

2 Answers2

3

There is no shortcut to achieve your goal that I know of, unless you are willing to:

  • Drop and recreate the database, or
  • Reset all Service Broker data in all queues, including dialogs themselves, in that database (in this case, you might try to follow Remus' advise in the linked question - ALTER DATABASE ... SET NEW_BROKER WITH ROLLBACK IMMEDIATE;)

Regarding your code, however, I would suggest using receive instead of select; otherwise you might get the same dialog multiple times. Also, you might want to distinguish EndDialog messages from any others if they occur in the queue.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • You are right, I understood I can't melt messages filled up in queue without `receive` with code above, Your second option was what I did. – ibubi Oct 03 '17 at 11:02
1

I had a similar issue where removing the services took really long. So long my SQL server ran out of memory and crashed. I first tried ending the dialog with the query you posted at the top, but that didn't help. In my case there were a lot of conversations created that were never closed. So although the queues were empty, there were still a lot conversations when I queryed sys.conversation_endpoints. I used the following script to clean up the conversations and after that I was able to delete it instantly.

Note that below query will close all conversations, no matter the queue so you need to filter if you only want to delete a specific one

declare @c uniqueidentifier
while(1=1)
begin
    select top 1 @c = conversation_handle from sys.conversation_endpoints
    if (@@ROWCOUNT = 0)
    break
    end conversation @c with cleanup
end
Marlou
  • 11
  • 1