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
----------------------------------------------------
-- 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
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]
