在我们开发service broker应用时候,可能用于测试或者客户端没有配置正确等导致服务端队列存在很多垃圾队列,不便于我们排查错误,我们可以使用SQL脚本来清空服务端这些垃圾数据:
USE
TestDB
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
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
也可以加使用事务加快清除队列速度,修改后的代码如下:
USE TestDB
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 @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
那么客户端接受到的消息如果没有处理,也会积攒在客户端队列中,其实就相当于许多未读邮件,我们可以使用以下脚本读取队列 ,读取后队列自动清空:
USE TestDB DECLARE @ReceiveDlgHandle UNIQUEIDENTIFIER ; DECLARE @ReceiveMsg NVARCHAR(255) ; DECLARE @ReceiveMsgName SYSNAME ; WHILE ( 1 = 1 ) BEGIN BEGIN TRANSACTION ; --获得队列中的数据 WAITFOR ( RECEIVE TOP (1) @ReceiveDlgHandle = conversation_handle, @ReceiveMsg = message_body, @ReceiveMsgName = message_type_name FROM dbo.Test_CC_TargetQueue ),TIMEOUT 1000 ; IF ( @@RowCount = 0 ) BEGIN ROLLBACK TRANSACTION ; BREAK ; END --解析队列中的数据,清除指定约定的队列 IF ( @ReceiveMsgName = 'Test_CC_Contract' ) BEGIN --发送消息到发送方 END CONVERSATION @ReceiveDlgHandle END COMMIT TRANSACTION ; END
传输队列中我们可以使用以下语句拼接出结束句柄会话:
/*
***** 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]
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查询窗口执行即可