1

I am getting constantly this error message on service broker:

POISON MSSAGE - Message Removed from the queue to allow other messages to proceed - ApplicationID: 377411

select top 1000
* from junofinance.[dbo].[servicebrokerlog] with(nolock)
order by logdate desc

select top 1000
* from junofinance.[dbo].[servicebrokerlog] with(nolock)
where message NOT LIKE 'POISON MSSAGE - Message Removed from the queue to allow other messages to proceed - ApplicationID: 377411'
order by logdate desc

enter image description here

----------------------------------------------------
-- service broker -- having a look
-- v.20171111
--marcello miorelli
----------------------------------------------------
set transaction isolation level read uncommitted
SELECT the_server = @@servername
      ,the_database = db_name()
      ,CE.conversation_handle
      ,CE.conversation_group_id
      ,ce.state_desc
      ,ce.far_service
      ,s.name as [Local Service]
      ,ce.far_broker_instance
      ,ce.is_initiator
      ,S.name   AS [Service Name]
      ,SC.name  as [Contract Name]
      ,SQ.name  as [Queue Name]
      ,sq.activation_procedure
      ,sq.is_activation_enabled
      ,sq.is_receive_enabled
      ,sq.is_enqueue_enabled
      ,sq.is_retention_enabled
      ,sq.is_poison_message_handling_enabled
      ,tq.enqueue_time
      ,tq.from_service_name
      ,tq.is_conversation_error
      ,tq.is_end_of_dialog
      ,tq.message_body
      ,tq.message_sequence_number
      ,tq.message_type_name
      ,tq.priority
      ,tq.service_contract_name
      ,tq.to_broker_instance
      ,tq.to_service_name
      ,tq.transmission_status

FROM sys.conversation_endpoints ce

LEFT OUTER JOIN sys.services s
             ON ce.service_id = s.service_id

LEFT OUTER JOIN sys.service_queues SQ
             ON S.service_queue_id = SQ.object_id

LEFT OUTER JOIN sys.service_contracts sc
             ON ce.service_contract_id = sc.service_contract_id

LEFT OUTER JOIN sys.transmission_queue TQ
             ON ce.conversation_handle = tq.conversation_handle

enter image description here

as you can see on the picture above I haven't got a poison message handling enabled. How to enable one???

Can you please help me troubleshoot this probably a case of poison message problem in the service broker?

There are lots of invoices coming through, I can't afford to lose anything!

This site below suggests a few interesting things:

Service Broker Poison Message Handling

there is this question here, but I am still investigating:

Service Broker: Queue Monitor is dropped after poison message

This is my procedure, please note the generation of the error message above, in the catch block inside the procedure:

CREATE PROCEDURE [dbo].[Usp_process_invoice_message]   
AS   
  BEGIN   
      DECLARE @applicationID INT,   
              @dialog_handle VARCHAR(max),   
              @messageType   NVARCHAR(max);   

      WHILE ( 1 = 1 )   
        BEGIN   
            BEGIN TRANSACTION;   

            WAITFOR ( receive TOP (1) @dialog_handle = conversation_handle,   
            @applicationid = message_body, @messageType = message_type_name FROM   
            [dbo].[invoiceTargetQueue] ), timeout 60000;   

            IF( @@ROWCOUNT = 0 )   
              BEGIN   
                  ROLLBACK TRANSACTION;   

                  BREAK;   
              END   

            SAVE TRANSACTION MessageReceivedSavepoint;   
            BEGIN try   
                IF ( @messageType = 'http://aifs.co.uk/InvoiceCreationRequest' )   
                  BEGIN   
                      INSERT INTO dbo.servicebrokerlog   
                                  ([message],   
                                   databasename,   
                                   source)   
                      VALUES     ( 'Application ready for invoice creation '   
                                   + CONVERT(VARCHAR(10), @applicationID),   
                                   Db_name(),   
                                   'usp_process_invoice_message');   

                      EXEC Usp_ins_invoiceandtransactions   
                        @applicationID;   
                  END   

                IF ( @messageType = 'DEFAULT'   
                      OR @messageType =   
            'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' )   
                  BEGIN   
                      END conversation @dialog_handle;   

                      INSERT INTO [dbo].[servicebrokerlog]   
                                  (message,   
                                   databasename,   
                                   source)   
                      VALUES     ('CONVERSATION CLOSED BY THE TARGET CH->'   
                                  + Cast(@dialog_handle AS VARCHAR(max)),   
                                  Db_name(),   
                                  'usp_process_invoice_message');   
                  END   

                COMMIT TRANSACTION;   
            END try   

            BEGIN catch   
                IF ( Error_number() = 1205 )   
                  BEGIN   
                      -- DEADLOCK,LETS TRY AGAIN   
                      ROLLBACK TRANSACTION messagereceivedsavepoint  

                      CONTINUE   
                  END   
                ELSE   
                  BEGIN   
                      DECLARE @ErrorMessage NVARCHAR(4000)   
                      DECLARE @ErrorSeverity INT   
                      DECLARE @ErrorState INT   
                      DECLARE @ErrorLine INT   
                      DECLARE @errorProc NVARCHAR(126)   
                      DECLARE @errorNum INT   

                      SELECT @ErrorMessage = Error_message(),   
                             @ErrorSeverity = Error_severity(),   
                             @ErrorState = Error_state(),   
                             @errorLine = Error_line(),   
                             @errorProc = Error_procedure(),   
                             @errorNum = Error_number()   

     ROLLBACK TRANSACTION ;   

                      INSERT INTO [dbo].[servicebrokerlog]   
                                  ([message],   
                                   databasename,   
                                   source)   
                      VALUES     (   
'POISON MSSAGE - Message Removed from the queue to allow other messages to proceed - ApplicationID: '  
+ CAST(@applicationID AS VARCHAR(50)),   
Db_name(),   
'usp_process_invoice_message');   

    --log the error message     
    INSERT INTO [dbo].[servicebrokererrorlog]   
                (errormessage,   
                 errorseverity,   
                 errorstate,   
                 errorline,   
                 errorproc,   
                 errornumber,   
                 databasename)   
    VALUES      ( @ErrorMessage,   
                  @ErrorSeverity,   
                  @ErrorState,   
                  @errorLine,   
                  @errorProc,   
                  @errorNum,   
                  Db_name() );   


END   
END catch   
END --while end   
END 

Another thing to look at:

set nocount on

if object_id('tempdb..#ErrLog') is not null
   drop table #ErrLog

CREATE TABLE #ErrLog (SeqNo bigint identity(1,1) not null,  LogDate datetime, ProcessInfo varchar(50), LogText varchar(max))

INSERT INTO #ErrLog (LogDate,ProcessInfo,LogText)
EXEC sp_readerrorlog

SELECT *
FROM #ErrLog 
ORDER BY LogDate desc, SeqNo desc

drop table #ErrLog

this is the only service broker relevant message that I have seen:

Service Broker login attempt by user 'MYCOMPANY_USA\sqlservice.' failed with error: 'A previously existing connection with the same peer was detected during connection handshake. This connection lost the arbitration and it will be closed. All traffic will be redirected to the previously existing connection. This is an informational message only. No user action is required. State 80.'. [SERVER: 10.31.9.999]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.