[SSB] clears the queue in the service broker.

When we develop a service broker application, it may be used for testing or the client is not correctly configured. As a result, many junk queues exist in the server Queue, which is not easy for us to troubleshoot, we can use the SQL script to clear the junk data on the server:

Use Testdb
Declare   @ Conversation   Uniqueidentifier

While Exists(Select 1 FromSYS. transmission_queue)

Begin

Set @ Conversation =(Select Top 1Conversation_handleFromSYS. transmission_queue)

EndConversation@ Conversation WithCleanup

End  

You can also use transactions to speed up the process of clearing the queue.CodeAs follows:

Use testdb
Declare @ conversation uniqueidentifier
Declare @ I Int = 0 ;

While (exists (select top1
Conversation_handle
From [sys]. [transmission_queue])
Begin
While (@ I <30)
Begin

Begin tran
Set @ conversation = (select top 1
Conversation_handle
From SYS. transmission_queue
)
End conversation @ conversation with cleanup
-- Print @ I
Commit
Set @ I = @ I + 1
End
Print @ I
Set @ I = 0
End

 

 

 

If the messages received by the client are not processed, they will also be accumulated in the client queue. In fact, this is equivalent to many unread emails. We can use the following script to read the queue. After reading the message, the queue is automatically cleared:

Use Testdb
Declare   @ Recvreplymsg   Nvarchar ( 100 );
Declare   @ Recvreplydlghandle   Uniqueidentifier ;

Begin   Transaction ;
While ( 1   =   1 )
Begin
Waitfor
(Receive Top ( 1 )
@ Recvreplydlghandle   = Conversation_handle,
@ Recvreplymsg   = Message_body
From DBO. test_targetqueue
), Timeout 1000 ;
If (@ rowcount = 0)
Begin
Rollback transaction;
Break;
End

EndConversation@ Recvreplydlghandle;

Select@ Recvreplymsg AsReceivedreplymsg;
End 

Commit Transaction;  

In the transmission queue, we can use the following statement to splice the end handle session:

 

/* ***** Script for selecttopnrows command from SSMs ***** */
Select top 1000 ' End conversation ''' + Cast (conversation_handle as nvarchar ( 50 ) + ''' With cleanup ' , Casted_message_body =
Case message_type_name when ' X '
Then cast (message_body as nvarchar (max ))
Else message_body
End
From [testdb]. [sys]. [transmission_queue]

Copy the first column and run it in the SQL Server query window.