SQL Server Broker Service Target Transmission Queue issue

Question

I've implemented two SSB across two different instances. They are data push pattern based on asynchronous triggers.

I am using SQL Server Enterprise 2008 R2, version info is found below

My SQL Version is Microsoft SQL Server Management Studio 10.0.4064.0 Microsoft Analysis Services Client Tools 10.0.4064.0 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 9.0.8112.16421 Microsoft .NET Framework 2.0.50727.5446 Operating System 6.1.7601

1. My queue is RETENTION set to off.

2. Sender Queue and sys.TransmissionQueue are empty.

3. Target Queue is empty ,but Target sys.transmissionqueue contains the 5000 messages I sent.

4. The table, data is inserted in @Target DB is locked. "When I select with nolock the 5000 records appears"

5. Profiler shows

@Sender

Broker:Conversation STARTED_OUTBOUND

Broker:Conversation CONVERSING

Broker:Message Classify

Broker:Remote Message Acknowledgement

Broker:Conversation Group

@Reciever

Broker:Conversation DISCONNECTED_OUTBOUND

Broker:Conversation Group

Broker:Message Classify

Broker:Remote Message Acknowledgement

Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 1

1. SSBDIAGNOSE shows no error.

I've used Rusanu's blog entry to troubleshoot the problem ,but I think my situation is different!

Hope anyone could help :)

Answer 1

Sounds like your target processing is receving messages, responding with and EndDialog, but not committing the transaction. This would explain the symptoms you describe (data is inserted but locked, target transmission queue has 5k messages not being sent). Can you confirm that the target processing is properly committing and ending the transactions?