在我们开发service broker应用时候,可能用于测试或者客户端没有配置正确等导致服务端队列存在很多垃圾队列,不便于我们排查错误,我们可以使用SQL脚本来清空服务端这些垃圾数据:
declare @conversation uniqueidentifier while exists (select 1 from sys.transmission_queue ) begin set @conversation = (select top 1 conversation_handle from sys.transmission_queue ) end conversation @conversation with cleanup
end
也可以加使用事务加快清除队列速度,修改后的代码如下:
DECLARE @conversation UNIQUEIDENTIFIER
DECLARE @i INT = 0 ;
WHILE ( EXISTS ( SELECT TOP 1
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
那么客户端接受到的消息如果没有处理,也会积攒在客户端队列中,其实就相当于许多未读邮件,我们可以使用以下脚本读取队列 ,读取后队列自动清空:
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 ;
BEGIN
ROLLBACK TRANSACTION ;
BREAK ;
END
END CONVERSATION @RecvReplyDlgHandle ;
SELECT @RecvReplyMsg AS ReceivedReplyMsg ;
END
COMMIT TRANSACTION ;
传输队列中我们可以使用以下语句拼接出结束句柄会话:
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]
然后复制第一列在sql server查询窗口执行即可