none
Service Broker - Skip Error Message and do NOT Disable the Queue RRS feed

  • Question

  • I'm using SQL2016 SP2. My objective is to a create Service Broker service using internal activation stored proc., that every time it gets any error, it will just rollback, store the error message into another table, skip that error message, and the service can process the next message. Unfortunately, I found when an error occur, the queue will also be disabled automatically, then I need to manually clear the queue by executing END CONVERSATION WITH CLEANUP for each error message inside the queue, otherwise I can't enable the queue again. But actually I don't want any manual intervention to enable the queue again. So my question is how can I stop SQL Server from disabling the queue even an fatal error occurred inside the internal activation stored proc.? Below is my testing:

    USE master;
    GO
    CREATE DATABASE [SBTEST3];
    GO
    ALTER DATABASE [SBTEST3] SET ENABLE_BROKER;
    GO
    USE [SBTEST3]
    GO
    CREATE MESSAGE TYPE [TestPoisonMessage] VALIDATION = NONE;
    GO
    CREATE CONTRACT [TestPoisonContract] ([TestPoisonMessage] SENT BY INITIATOR);
    GO
    CREATE QUEUE [dbo].[TestPoisonQueue] WITH STATUS = OFF;
    GO
    CREATE SERVICE [TestPoisonInitiator] ON QUEUE [dbo].[TestPoisonQueue];
    GO
    CREATE SERVICE [TestPoisonTarget] ON QUEUE [dbo].[TestPoisonQueue] ([TestPoisonContract]);
    GO
    CREATE TABLE [dbo].[SBAuditLog] (
    	[message] nvarchar(4000) NULL,
    	[logTime] datetime2(2) NOT NULL DEFAULT GETDATE(),
    	[err_num] int NULL,
    	[err_msg] nvarchar(4000) NULL
    )
    GO
    CREATE OR ALTER PROC [dbo].[uspTestPoison]
    AS
    SET NOCOUNT ON;
    
    DECLARE @message_type varchar(100), @dialog uniqueidentifier, @message_body nvarchar(4000);
      
    BEGIN TRY
    
    	BEGIN TRAN;	
    
    	WAITFOR ( 
    		RECEIVE TOP(1)
    			@message_type = message_type_name,
    			@message_body = CAST(message_body AS nvarchar(4000)),
    			@dialog = [conversation_handle]
    		FROM [dbo].[TestPoisonQueue]
    	), TIMEOUT 500
    	;
    
    	IF (@@ROWCOUNT != 0 AND @message_body IS NOT NULL)
    	BEGIN
    		IF @message_type = 'TestPoisonMessage'
    		BEGIN
    			INSERT INTO SBAuditlog ([message]) VALUES (@message_body);
    
    			-- Fatal Error
    			RAISERROR ('Fatal Error', 25, 1) WITH LOG;
    		END
    
    		END CONVERSATION @dialog;
    	END
    
    	COMMIT;
    
    END TRY
    BEGIN CATCH
    
    	DECLARE @error int, @message nvarchar(4000);
    	SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE();
    
    	-- Uncommitable transaction
    	IF XACT_STATE() = -1
    	BEGIN
    		ROLLBACK;
    	END
    	-- Commitable transaction
    	ELSE IF XACT_STATE() = 1
    	BEGIN
    		COMMIT;
    	END
    
    	-- Failure audit log
    	INSERT INTO SBAuditlog ([message], err_num, err_msg) VALUES (@message_body, @error, @message);
    
    	-- End Conversion with Error
    	END CONVERSATION @dialog WITH error = @error DESCRIPTION = @message;
    
    END CATCH
    GO
    ALTER QUEUE [dbo].[TestPoisonQueue] WITH STATUS = ON, ACTIVATION (STATUS = ON, PROCEDURE_NAME = [dbo].[uspTestPoison], MAX_QUEUE_READERS = 1, EXECUTE AS OWNER);
    GO
    
    DECLARE @Handle UNIQUEIDENTIFIER;
    BEGIN DIALOG CONVERSATION @Handle
    FROM SERVICE [TestPoisonInitiator]
    TO SERVICE 'TestPoisonTarget'
    ON CONTRACT [TestPoisonContract]
    WITH ENCRYPTION = OFF;
    SEND ON CONVERSATION @Handle
    MESSAGE TYPE [TestPoisonMessage] (N'Testing Message');

    After running the above SEND command, the queue will be disabled. I need to run below command to generate the END CONVERSATION WITH CLEANUP statement(s), in order to clear it, and then I can re-enable it. (BUT THAT'S NOT WHAT I WANT!):

    SELECT name, is_receive_enabled, is_enqueue_enabled FROM sys.service_queues;
    SELECT 'END CONVERSATION ''' + CAST([conversation_handle] AS varchar(100)) + ''' WITH CLEANUP;' FROM [dbo].[TestPoisonQueue];
    END CONVERSATION '...' WITH CLEANUP;
    ALTER QUEUE [dbo].[TestPoisonQueue] WITH STATUS = ON;
    SELECT name, is_receive_enabled, is_enqueue_enabled FROM sys.service_queues;
    Wednesday, October 3, 2018 6:26 AM

Answers

  • You do not need to be that brutal, but you must take the message of the queue and commit the RECEIVE. By default, five consecutive rollbacks on a queue causes the queue to be disabled. You can disable poison-message handling, but it is not really going to make you any happier, as you will keep on processing the same bad row.

    I have written about error handling in Service Broker here: http://www.sommarskog.se/error_handling/Appendix3.html

    The article as such is not very long, but it builds on a longer example and other discussion in the main article.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by nonno Thursday, October 4, 2018 1:12 AM
    Wednesday, October 3, 2018 9:51 PM