StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POService Broker leaving closed items in sys.conversation_endpoints after 30 mins
text
Body
copied!<p>I have a single machine service broker set up using internal activation, the broker is processing items that are placed on the queue and appears to be closing the conversations correctly (see profile details below).</p> <p>A single record left in sys.conversation_endpoints in a CD - CLOSED state with a security_timestamp that is set 30 mins in the future but this record is never deleted.</p> <p>I also have my own log shows the what its happening the only thing that appears strange is that the initiator procedure gets called with null values for conversation_handle, message_type and message_body (last item in my log information)</p> <p>I have included a script (name changed not indicative of actual names) that will create all the objects I'm using, and to mimic how I am using them simply execute:</p> <pre><code>dbo.usp_QueueRequest 2, null, 'Trial' </code></pre> <p>That should then give you the same results that I have been seeing consistently</p> <p>Trace:</p> <pre><code>EventClass TextData SPID Event Sequence EventSubClass Broker:Activation 30 1394 1 - Start Broker:Conversation CONVERSING 30 1395 1 - SEND Message Broker:Message Classify 30 1396 1 - Local Broker:Conversation CONVERSING 30 1397 6 - Received Sequenced Message Broker:Activation 31 1398 1 - Start Broker:Conversation DISCONNECTED_OUTBOUND 31 1399 2 - END CONVERSATION Broker:Conversation Group 31 1400 2 - Drop Broker:Message Classify 31 1401 1 - Local Broker:Conversation DISCONNECTED_INBOUND 31 1402 7 - Received END CONVERSATION Broker:Conversation CLOSED 31 1403 10 - Received END CONVERSATION Ack Broker:Conversation CLOSED 30 1404 2 - END CONVERSATION Broker:Conversation Group 30 1405 2 - Drop Broker:Activation 30 1406 2 - Ended Broker:Activation 31 1407 2 - Ended </code></pre> <p>My log</p> <pre><code>Sequence Description 1 Target activation for message type: RequestMessage 2 Call fake processing procedure 3 In fake processing procedure 4 Send ReplyMessage for conversation 8B355311-5D9F-E111-93EC-A4BADBFBB13B 5 Initiator activation 6 Initiator conversation 88355311-5D9F-E111-93EC-A4BADBFBB13B message type: ReplyMessage 7 Initiator received reply from target, end converstion 8 Initiator activation 9 Target activation for message type: http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog 10 Target received end dialog 11 Initiator No conversation message type:No message type </code></pre> <p>sys.converstation_endpoints record (shortened)</p> <pre><code>conversation_handle is_initiator state state_desc far_service security_timestamp dialog_timer 8B355311-5D9F-E111-93EC-A4BADBFBB13B 0 CD CLOSED InitiatorService 2012-05-16 14:13:38.923 1900-01-01 00:00:00.000 </code></pre> <p>Script to create objects (names changed, not actual names)</p> <pre><code>IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Log]') AND type in (N'U')) DROP TABLE [dbo].[Log] GO CREATE TABLE [dbo].[Log]( [Id] [int] IDENTITY(1,1) NOT NULL, [Description] [varchar](max) NULL, [DateTime] [date] NULL DEFAULT (getdate())) GO IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BrokerUser') DROP USER [BrokerUser] GO CREATE USER [BrokerUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo] GO IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'RequestMessage') CREATE MESSAGE TYPE [RequestMessage] VALIDATION = WELL_FORMED_XML IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'AReplyMessage') CREATE MESSAGE TYPE [ReplyMessage] VALIDATION = WELL_FORMED_XML GO IF NOT EXISTS (SELECT * FROM sys.service_contracts WHERE name =N'MessageContract') CREATE CONTRACT [MessageContract] ([RequestMessage] SENT BY INITIATOR, [ReplyMessage] SENT BY TARGET) GO IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TargetQueue') CREATE QUEUE TargetQueue GO IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TargetQueue') GRANT RECEIVE ON [TargetQueue] TO [BrokerUser] GO IF NOT EXISTS (SELECT * FROM sys.services WHERE name = N'TargetService') CREATE SERVICE [TargetService] ON QUEUE TargetQueue ([MessageContract]) GO IF EXISTS (SELECT * FROM sys.services WHERE name = N'TargetService') GRANT SEND ON SERVICE::[TargetService] TO [BrokerUser] GO IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'InitiatorQueue') CREATE QUEUE InitiatorQueue; GO IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'InitiatorQueue') GRANT RECEIVE ON [InitiatorQueue] TO [BrokerUser] GO IF NOT EXISTS (SELECT * FROM sys.services WHERE name = N'InitiatorService') CREATE SERVICE [InitiatorService] ON QUEUE InitiatorQueue GO IF EXISTS (SELECT * FROM sys.services WHERE name = N'InitiatorService') GRANT SEND ON SERVICE::[InitiatorService] TO [BrokerUser] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FakeProcessing]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_FakeProcessing] GO CREATE PROCEDURE [dbo].[usp_FakeProcessing] AS BEGIN insert [Log] ([Description]) VALUES ('In fake processing procedure'); END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TargetActivation]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_TargetActivation] GO CREATE PROCEDURE [dbo].[usp_TargetActivation] AS DECLARE @Handle UNIQUEIDENTIFIER; DECLARE @Message XML; DECLARE @MessageType sysname; WHILE (1=1) BEGIN BEGIN TRANSACTION; WAITFOR ( RECEIVE TOP(1) @Handle = conversation_handle, @Message = message_body, @MessageType = message_type_name FROM TargetQueue ), TIMEOUT 5000; IF (@@ROWCOUNT = 0) BEGIN ROLLBACK TRANSACTION; BREAK; END INSERT [Log] ([Description]) VALUES ('Target activation for message type: ' + + COALESCE(@MessageType,'No message type')) -- it's an error message (some error occurred on the initiator side). --This type of message is automatically created by SB when an END CONVERSATION command is executed with the WITH ERROR clause for the initiator IF @MessageType =N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' BEGIN ROLLBACK TRANSACTION; -- roll back the message transaction. In this example there was nothing done to roll back but it is good practice -- in the event of an error INSERT [Log] ([Description]) VALUES ('Error occured with SB') END CONVERSATION @Handle; -- and end the conversation (dialog) on this side (target) BREAK; -- break out of the loop (and end the stored proc) END ELSE IF @MessageType = N'RequestMessage' BEGIN INSERT [Log] ([Description]) VALUES ('Call fake procedure') EXEC [usp_FakeProcessing]; INSERT [Log] ([Description]) VALUES ('Add reply message for conversation ' + CAST(@Handle AS varchar(80)) + ' with message : ' + CAST(@Message as VARCHAR(4000))); SEND ON CONVERSATION @Handle MESSAGE TYPE [ReplyMessage] (@message) END ELSE IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN INSERT [Log] ([Description]) VALUES ('Target received end dialog') END CONVERSATION @Handle; END COMMIT TRANSACTION; END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InitiatorActivation]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_InitiatorActivation] GO CREATE PROCEDURE [dbo].[usp_InitiatorActivation] AS DECLARE @Handle UNIQUEIDENTIFIER; DECLARE @Message XML; DECLARE @MessageType sysname; BEGIN INSERT [Log] ([Description]) VALUES ('Initiator activation') WAITFOR ( RECEIVE TOP(1) @Handle = conversation_handle, @Message = message_body, @MessageType = message_type_name FROM InitiatorQueue ), TIMEOUT 5000; INSERT [Log] ([Description]) VALUES ('Initiator ' + ISNULL(CAST(@Handle AS VARCHAR(100)) , 'No handle') + ' message type:' + COALESCE(@MessageType,'No message type') + ' Body: ' + COALESCE(CAST(@Message AS varchar(1000)),'No body')) IF @Handle IS NOT NULL BEGIN IF @MessageType = N'ReplyMessage' BEGIN INSERT [Log] ([Description]) VALUES ('Initiator received reply from target, end converstion') END CONVERSATION @Handle; END IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN INSERT [Log] ([Description]) VALUES ('Initiator received end dialog message') END CONVERSATION @Handle; END END END GO GRANT EXECUTE ON [usp_TargetActivation] to [BrokerUser] GO GRANT EXECUTE ON [usp_InitiatorActivation] to [BrokerUser] GO IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TargetQueue') ALTER QUEUE TargetQueue WITH ACTIVATION ( STATUS = ON, PROCEDURE_NAME = [usp_TargetActivation], MAX_QUEUE_READERS = 1, EXECUTE AS 'BrokerUser' ); GO IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'InitiatorQueue') ALTER QUEUE InitiatorQueue WITH ACTIVATION ( STATUS = ON, PROCEDURE_NAME = [usp_InitiatorActivation], MAX_QUEUE_READERS = 1, EXECUTE AS 'BrokerUser' ); GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_QueueRequest]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_QueueRequest] GO CREATE PROCEDURE [dbo].[usp_QueueRequest] @numericvalue INT, @StartDate DATE, @TriggeredBy VARCHAR(50) AS BEGIN DECLARE @Handle UNIQUEIDENTIFIER; DECLARE @RequestMsg NVARCHAR(100); DECLARE @MessageBody XML DEClare @Params TABLE ( ReadingDate SMALLDATETIME, SiteNumber INT, TriggeredBy VARCHAR(50) ) INSERT INTO @Params(ReadingDate,SiteNumber, TriggeredBy) VALUES(@StartDate, @numericvalue, @TriggeredBy) SELECT @MessageBody = (SELECT * FROM @Params FOR XML PATH ('Params'), TYPE); BEGIN TRANSACTION; BEGIN DIALOG @Handle FROM SERVICE [InitiatorService] TO SERVICE 'TargetService' ON CONTRACT [MessageContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @Handle MESSAGE TYPE [RequestMessage] (@MessageBody); COMMIT TRANSACTION; END GO </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload