Sys.Conversation_Endpoints

Oct 12, 2006

I know that if a conversation is normally ended the handle will wind up in the sys.Conversation_endpoints table in a Closed State. I realize they are supposed to stay there for 30 minutes to prevent a reply attack, however the number of rows I have in this table continues to grow with the bulk of the states set to 'Closed'. I am trying to use this table to determine if I have any conversation handle leaks. I see some rows in there with a Disconnected Outbound state while some are conversing. What should I be looking for in this table and how can I know I have a problem (ie. leak). I realize that Disconnected Outbound is probably something i need to look into. Are there any other states I should be concerned with?

Gary

View 9 Replies


ADVERTISEMENT

Sys.conversation_endpoints

Oct 21, 2005

Is there anyway to figure out which tables/objects this view uses?

View 11 Replies View Related

More Conversation_endpoints

Oct 26, 2006

So I took the time to build a reproduction of the conversation_endpoint problem that was discussed in another thread. I build two databases, with a send and receive queue. This is essentially the way the code works here at my site. I have a script near the bottom that sends messages every 5 minutes for 2 hours. If there is any logic that removes conversation_endpoints 30 min then the Message record table will show them.

Please let me know what I am doing wrong, so I can change my production code to help eliminate the large buildup in the sys.conversation_endpoints.

Thanks!



use master

go

if exists ( select * from sys.databases where name = 'SBSource' )

drop database SBSource

go

if exists ( select * from sys.databases where name = 'SBTarget' )

drop database SBTarget

go

-- Setup environment for test

create database SBSource

GO

ALTER DATABASE SBSource SET ENABLE_BROKER

ALTER DATABASE SBSource SET TRUSTWORTHY ON

GO

create database SBTarget

GO

ALTER DATABASE SBTarget SET ENABLE_BROKER

ALTER DATABASE SBTarget SET TRUSTWORTHY ON

GO

use SBSource

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[SourceQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBSourceTest] authorization [dbo]

ON QUEUE [dbo].[SourceQueue]

( [Test] );

CREATE ROUTE [ToTarget] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBTargetTest' , ADDRESS = N'LOCAL';

GO

create procedure dbo.ProcessEndDialogMessages

as

begin

set nocount on;

declare @conversation_handle uniqueidentifier,

@message_type sysname,

@message_body xml;

begin transaction;

WAITFOR (

RECEIVE @conversation_handle = [conversation_handle],

@message_type = [message_type_name],

@message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

while @conversation_handle IS NOT NULL

begin

end conversation @conversation_handle;



commit;

begin transaction;

set @conversation_handle = null;

WAITFOR (

RECEIVE @conversation_handle = [conversation_handle],

@message_type = [message_type_name],

@message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

end



commit;

end

go

Alter QUEUE dbo.[SourceQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessEndDialogMessages, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go



use SBTarget

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[TargetQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBTargetTest] authorization [dbo]

ON QUEUE [dbo].[TargetQueue]

( [Test] );

CREATE ROUTE [ToSource] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBSourceTest' , ADDRESS = N'LOCAL';

GO

create table dbo.MessageRecord ( Conversation_handle uniqueidentifier, Inserted datetime )

go

create procedure dbo.ProcessTargetQueue

as

begin

set nocount on;

declare @conversation_handle uniqueidentifier,

@message_type sysname,

@message_body xml;

begin transaction;

WAITFOR (

RECEIVE @conversation_handle = [conversation_handle],

@message_type = [message_type_name],

@message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

while @conversation_handle IS NOT NULL

begin

insert into dbo.MessageRecord ( Conversation_handle, Inserted ) values ( @conversation_handle, getdate() );

end conversation @conversation_handle;



commit;

begin transaction;

set @conversation_handle = null;

WAITFOR (

RECEIVE @conversation_handle = [conversation_handle],

@message_type = [message_type_name],

@message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

end



commit;

end

GO

Alter QUEUE dbo.[TargetQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessTargetQueue, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go

use sbsource

go



-- start sending messages, check count in conv_endpoints along the way

set xact_abort on

set nocount on

declare @EndAt datetime,

@msg xml,

@ch uniqueidentifier;

set @EndAt = DATEADD( hh, 2, getdate() )

set @msg = '<message>dfsafa</message>';

while getdate() < @EndAt

begin

set @ch = null;

begin transaction;

begin dialog conversation @ch

from service [SBSourceTest]

to service 'SBTargetTest'

on contract [Test]

with

encryption=off;

send on conversation @ch message type [msgTest] (@msg);

--- note the abscence of an end conversation, so no fire and forget!

commit;

waitfor delay '00:05:00'

end

GO

-- check on data after complete.

select state, count(*)

from SBSource.sys.conversation_endpoints

group by state;

select state, count(*)

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

group by state;

select m.*, c.*

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

select *

from SBTarget.dbo.targetqueue

View 1 Replies View Related

Why Are Sys.transmission_queue And Sys.conversation_endpoints Filling Up??

Jul 25, 2007

hi all!



I have 2 instances that communicate via service broker.

The conversations are only one way from initiator server1 db to target server2 db.

I also reuse dialog id's in BEGIN DIALOG @dlgId

i save @dlgId from the first run into a table and then retreive it for each message send

since the messages are constant i don't close the dialog at the target for each message.



i'm just wodering why do both sys.transmission_queue and sys.conversation_endpoints get a row

for each message i send but the transmission_status in sys.transmission_queue is emtpy.



also each conversation_handle and conversation_id is different for each row and

only one row in each sys table has the same conversation_handle as my saved @dlgId.



just wondering what is going on.



this code is done on the initiator



DECLARE @dlgId UNIQUEIDENTIFIER

-- each database has one dialog id

SELECT @dlgId = DialogId

FROM dbo.Dialogs

WHERE DbId = DB_ID()

-- Begin the dialog, either with existing or new Id

BEGIN DIALOG @dlgId

FROM SERVICE [//DataSender] -- service on initiator server

TO SERVICE '//DataWriter', -- service on target server

-- Target's Service Broker Id

'83382A22-2830-4B25-B067-15AAC255EB03'

ON CONTRACT [//Contract1]

WITH ENCRYPTION = OFF;

-- Send data

;SEND ON CONVERSATION @dlgId

MESSAGE TYPE [//Message1] (@msg)



Thanx,

Mladen

View 7 Replies View Related

Placement Of Sys.conversation_endpoints And Sys.transmission_queue

Dec 21, 2006

Is there any thought going into moving these two tables to a file group that we can control? Putting this in Primary with the rest of my system tables is quite problematic, and hinders my ability to manage space usage on my files. Traditionally, we didn't have to consider a primary file group that could grow to large proportions, but now with these two tables it can. If a large volume of messages gets sent through and the system can't keep up, then these tables and my primary file group will grow sometimes enormously.

View 8 Replies View Related

Cannot See Data From SYS.CONVERSATION_ENDPOINTS Over A Linked Server

May 21, 2007

Hello,



I have a server that is used to query other servers to ensure that things are functioning correctly. On some of our servers we are running ServiceBroker and some of the monitoring involves querying the SYS.CONVERSATION_ENPOINTS view on these servers.

When I query the SYS.CONVERSATION_ENPOINTS view over a linked server it returns zero rows, though when I run the same query locally it returns data. I initially thought it was a permission issue but it seems I can see data across the linked server to other system views within the sys schema (ie sys.all_objects).



Is there something different/special about SYS.CONVERSATION_ENDPOINTS that prevents me from seeing its data across linked servers or have I simply got permission problems.



Thanks



Ian

View 2 Replies View Related

Target Sys.conversation_endpoints Not Purged Of Closed Conversations

Dec 7, 2006

I hope someone can help me with this as we plan on using Service Broker in a high volume production environment. The script that builds everything is available if it's needed to diagnose the problem I'm having.

I'm having an issue where sys.conversation_endpoints on the target side of a conversation is never getting purged of closed conversations even after the 30 minute delay. The view is filled with closed conversations and database size is growing every day. I'm aware I can end conversation with cleanup on these conversations, but I would prefer that Service Broker behaves as expected. I'm also aware of the problems with the fire and forget model, but my model is request/response/end between 2 databases on the same server instance. Here's the typical series of events:

Initiator sends request
Target receives request
Target processes request
Target sends response
Initiator receives response
Initiator processes response
Initiator ends conversation
Target receives EndDialog message
Target ends conversation

Occasionally during the target's processing of a request, an exception is caught and the Target ends the conversation with an error:

Initiator sends request
Target receives request
Target processes request and recognizes error
Target ends conversation with error
Initiator receives EndDialog message
Initiator ends conversation

Here's the trace where Database ID 23 is initiator and 24 is target, no error:










EventClass
DatabaseID
TextData
EventSubClass

Broker:Conversation Group
23

1 - Create

Broker:Conversation
23
STARTED_OUTBOUND
11 - BEGIN DIALOG

Broker:Conversation
23
CONVERSING
1 - SEND Message

Broker:Message Classify
23

1 - Local

Broker:Conversation Group
24

1 - Create

Broker:Conversation
24
STARTED_INBOUND
12 - Dialog Created

Broker:Conversation
24
CONVERSING
6 - Received Sequenced Message

Broker:Activation
24

1 - Start

Broker:Conversation
24
CONVERSING
1 - SEND Message

Broker:Message Classify
24

1 - Local

Broker:Conversation
23
CONVERSING
6 - Received Sequenced Message

Broker:Activation
23

1 - Start

Broker:Conversation
23
DISCONNECTED_OUTBOUND
2 - END CONVERSATION

Broker:Conversation Group
23

2 - Drop

Broker:Message Classify
23

1 - Local

Broker:Conversation
24
DISCONNECTED_INBOUND
7 - Received END CONVERSATION

Broker:Conversation
23
CLOSED
10 - Received END CONVERSATION Ack

Broker:Conversation
24
CLOSED
2 - END CONVERSATION

Broker:Conversation Group
24

2 - Drop

Broker:Activation
23

2 - Ended

Broker:Activation
24

2 - Ended

Here are the typical records in the target sys.conversation_endpoints. These records never disappear:










Normal
With Error

conversation_handle
3FE27EE5-1E86-DB11-B009-000BDB714730
53E17EE5-1E86-DB11-B009-000BDB714730

conversation_id
0A432392-55F5-461B-87D5-0058795BC3AE
BCCDFA85-86A3-43B8-9648-24FFE5C0ED3F

is_initiator
0
0

service_contract_id
0
0

conversation_group_id
00000000-0000-0000-0000-000000000000
00000000-0000-0000-0000-000000000000

service_id
0
0

lifetime
2074-12-25 21:29:28.640
2074-12-25 21:29:28.000

state
CD
CD

state_desc
CLOSED
CLOSED

far_service
http://my.domain.com/schemas/test/Initiator/2006-12-07
http://my.domain.com/schemas/test/Initiator/2006-12-07

far_broker_instance
227D0898-0399-40E0-954B-C8B685EE415A
227D0898-0399-40E0-954B-C8B685EE415A

principal_id
5
5

far_principal_id
6
6

outbound_session_key_identifier
DEBEB4DB-D186-410B-9555-A34F8F5C9FE2
B82BB074-5AE5-4164-9D0B-53E364B0B52B

inbound_session_key_identifier
1DBAE307-5DFF-4050-9D94-71003D8BD058
57B5C7D8-9E8B-4614-9325-5AA30AED3670

security_timestamp
2006-12-07 18:45:52.763
1900-01-01 00:00:00.000

dialog_timer
1900-01-01 00:00:00.000
1900-01-01 00:00:00.000

send_sequence
1
1

last_send_tran_id
0x550800000000
0x700700000000

end_dialog_sequence
-1
1

receive_sequence
2
1

receive_sequence_frag
0
0

system_sequence
0
0

first_out_of_order_sequence
-1
-1

last_out_of_order_sequence
0
0

last_out_of_order_frag
0
0

is_system
0
0

View 9 Replies View Related

Why Is Sys.conversation_endpoints Filling Up Even When Reusing Dialog Conversations

Aug 5, 2007

Hi! I'm wondering why is my sys.conversation_endpoints table inserting a new row for each message i send even when i reuse conversations?
when i send the first message i get the first row in the sys.conversation_endpoints with a uniqueidentifier for the conversation_handle. this uniqueidentifier is then saved in the table which i query the next time i send a message to reuse the dialog conversation.
But even though it looks like the uniqueidentifier is reused i still get a new row for every message i send with a different conversation_handle?
this happens in both target and initator db.

I've tried to understand this by i don't.

Also for the moment i don't end conversations. But as i understand it this shouldn't matter.

Also the message successfully arives to the target and sys.transmission_queue is empty in both databases.
Neither queues have any error messages in them.

Thanx

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved