SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HELP! Service Broker is dropping messages


HELP! Service Broker is dropping messages

Author
Message
ericjorg
ericjorg
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 40
I've set up a simple service broker implementation where I have a sender queue on one database and a reciever queue on another database. I've noticed that randomly, about 50% of the messages are dropped and do not show up in either the sender or reciever queues, even after waiting several minutes.

To test this, I run a query that puts four messages in the queue, then shows the contents of the sender and receiver queues. After sending four messages, I never see anything in the sender queue, but I'll see anwhere from 1-4 messages in the receiver queue and there is no pattern to what messages are getting dropped.

I tried tracing all of the broker messages and don't see any indication of errors. Instead, I see this pattern of traces show up for all four messages, regardless of whether they are dropped or not:

STARTED_OUTBOUND
CONVERSING
STARTED_INBOUND
CONVERSING
DISCONNECTED_OUTBOUND
DISCONNECTED_INBOUND
CLOSED

I am running out of ideas of how to get brokering to work right. Any advice?

-e

Here's my test script:
Use SenderDatabase

EXEC [MySignaling].[SendSignal] '<Foo>A1</Foo>'
EXEC [MySignaling].[SendSignal] '<Foo>B1</Foo>'
EXEC [MySignaling].[SendSignal] '<Foo>C1</Foo>'
EXEC [MySignaling].[SendSignal] '<Foo>D1</Foo>'

SELECT * From MySignaling.SignalDefaultSenderQueue

Use ReceiverDatabase
SELECT * From MySignaling.SignalReceiverQueue



Here is the SendSignal SP:

CREATE PROCEDURE [MySignaling].[SendSignal]
(
@signal XML,
@senderService SYSNAME = 'MySignaling_SignalDefaultSenderService'
)
AS
DECLARE @SBDialog UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @SBDialog
FROM SERVICE @senderService
TO SERVICE 'MySignaling_SignalReceiverService' ON CONTRACT [MySignaling_SignalContract] WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @SBDialog MESSAGE TYPE [MySignaling_Signal] (@signal);
END CONVERSATION @SBDialog;
RETURN;



And here's the setup code for the two databases:


ALTER DATABASE ReceiverDatabase SET ENABLE_BROKER
Go

ALTER DATABASE ReceiverDatabase SET TRUSTWORTHY ON
Go

ALTER DATABASE SenderDatabase SET ENABLE_BROKER
Go

ALTER DATABASE SenderDatabase SET TRUSTWORTHY ON
Go

---------------------------------------------------
-- Set up Receiver
---------------------------------------------------
USE ReceiverDatabase
Go

CREATE SCHEMA [MySignaling];
Go

CREATE MESSAGE TYPE [MySignaling_Signal] VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [MySignaling_SignalContract] ([MySignaling_Signal] SENT BY INITIATOR);
GO

CREATE QUEUE [MySignaling].[SignalReceiverQueue]
WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = OFF);
GO

CREATE SERVICE [MySignaling_SignalReceiverService]
ON QUEUE [MySignaling].[SignalReceiverQueue] ([MySignaling_SignalContract]);
Go

---------------------------------------------------
-- Set up Sender
---------------------------------------------------

USE SenderDatabase
Go

CREATE SCHEMA [MySignaling];
Go

CREATE MESSAGE TYPE [MySignaling_Signal] VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [MySignaling_SignalContract] ([MySignaling_Signal] SENT BY INITIATOR);
GO

CREATE QUEUE [MySignaling].[SignalDefaultSenderQueue]
WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = OFF);
GO

CREATE SERVICE [MySignaling_SignalDefaultSenderService]
ON QUEUE [MySignaling].[SignalDefaultSenderQueue] ([MySignaling_SignalContract]);
GO

CREATE PROCEDURE [MySignaling].[SendSignal]
(
@signal XML,
@senderService SYSNAME = 'MySignaling_SignalDefaultSenderService'
)
AS
DECLARE @SBDialog UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @SBDialog
FROM SERVICE @senderService
TO SERVICE 'MySignaling_SignalReceiverService' ON CONTRACT [MySignaling_SignalContract] WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @SBDialog MESSAGE TYPE [MySignaling_Signal] (@signal);
END CONVERSATION @SBDialog;
RETURN;





ericjorg
ericjorg
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 40
Figured it out: There was another instance of the receiver queue that I did not know about, and SQL Server was load balancing between the instances.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search