how to clean all messages of a queue ?

how to clean all messages of a queue ?

• Jaaaaa

  The RECEIVE statement (as it exists today) only fetches messages belonging to a single conversation group at a time. So the only way to get rid of all messages is to call RECEIVE in a loop:
  
  WHILE (1)
  BEGIN
     RECEIVE * FROM ExpenseQueue
     IF (@@ROWCOUNT = 0)
        BREAK
  END
  
  Are you just looking at means to clean up the queue, or do you really want to fetch multiple conversation groups in a single batch. If it is the latter, could you explain your requirements further to help us improve the product in future versions
  
  Thanks,
  Rushi
  --
  Developer, Service Broker Team, SQL Server Engine
  Microsoft Corporation

  
  

• KenHay

  scripts of Roger_MS can solve my problem, but seems it clean all messages of all queues. If I want to clean a queue only, should I use my Queue_Name to replace sys.conversation_endpoints Just as
  
  declare conv cursor for select conversation_handle from Queue_Name
  
  And I think it is convenient to use a statement like "alter queue XXX with cleanup" .
  
  Thank you.
  
  

• Sebastian Mares

  Rushi's answer assumes that you indeed just want to get rid of the messages and leave the conversations active.  If you also want to clean up the conversations - say you have run a bunch of tests and found your application doesn't work correctly - you might want to try one of these scripts which work in ascending dgrees of severity.  If you truely don't care at all about the messages or conversations, try the last one:
  
  

  -- End conversations that are in an error state

  declare @handle uniqueidentifier

  declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'ER'

  open conv

  fetch NEXT FROM conv into @handle

  while @@FETCH_STATUS = 0

  Begin

  END Conversation @handle with cleanup

  fetch NEXT FROM conv into @handle

  End

  close conv

  deallocate conv

   

  -- End conversations that are half closed

  declare @handle uniqueidentifier

  declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'DI'

  open conv

  fetch NEXT FROM conv into @handle

  while @@FETCH_STATUS = 0

  Begin

  END Conversation @handle with cleanup

  fetch NEXT FROM conv into @handle

  End

  close conv

  deallocate conv

   

   

  -- End all conversations

  declare @handle uniqueidentifier

  declare conv cursor for select conversation_handle from sys.conversation_endpoints

  open conv

  fetch NEXT FROM conv into @handle

  while @@FETCH_STATUS = 0

  Begin

  END Conversation @handle with cleanup

  fetch NEXT FROM conv into @handle

  End

  close conv

  deallocate conv

  
  

• Trojanfan20

  That would clean up one end of the conversations in the queue.  Keep in mind that conversations always involve 2 queues so if you want to clean up the conversations you will have to clean up both queues.  This is definitely not something you want to do regularly.  Clearing out a queue is equivalent to truncating a table - there will often be valuable data destroyed if you do this so this would generally be something you do only during development.
  
• how to clean all messages of a queue ?