is there any advantage when troubleshooting service broker, to create the queue with retention=ON?

Question

from CREATE QUEUE (Transact-SQL):

RETENTION Specifies the retention setting for the queue. If RETENTION = ON, all messages sent or received on conversations that use this queue are retained in the queue until the conversations have ended. This lets you retain messages for auditing purposes, or to perform compensating transactions if an error occurs. If this clause is not specified, the retention setting defaults to OFF.

when I have to do some Service Broker Troubleshooting

I have a look at these:

-- Message Types
SELECT * 
FROM sys.service_message_types;
-- Contracts
SELECT * 
FROM sys.service_contracts;

-- Queues
SELECT * 
FROM sys.service_queues;

-- Services
SELECT * 
FROM sys.services;

-- Endpoints
SELECT * 
FROM sys.endpoints;

I check this:

SELECT conversation_handle, is_initiator, s.name as 'local service', 
far_service, sc.name 'contract', state_desc
FROM sys.conversation_endpoints ce
LEFT JOIN sys.services s
ON ce.service_id = s.service_id
LEFT JOIN sys.service_contracts sc
ON ce.service_contract_id = sc.service_contract_id;


-- Error messages in the queue
SELECT * 
FROM sys.transmission_queue;

QUESTION: Is there any advantage in setting RETENTION=ON for service broker queues?

as further info: Where would the message be stored? For how long?

thank you

Recommendations on how to organize Queues in Service Broker

The following Chinese article shows an example of retention=on:

在创建QUEUE的时候，指定retention也很重要。。

1. 如果retention=on.那么即使message被receive之后，还是留在了queue里面，直到这个conversation被end

ALTER QUEUE [dbo].[targetQueue] WITH STATUS = ON , RETENTION = ON , POISON_MESSAGE_HANDLING (STATUS = ON) 

---

作者：dbLenis

来源：CSDN

原文：https://blog.csdn.net/wujiandao/article/details/7288864

版权声明：本文为博主原创文章，转载请附上博文链接！

Answer 1

Setting RETENTION=ON for Service Broker queues can provide advantages in certain scenarios:

Auditing: When retention is enabled, all messages sent or received on conversations using the queue are retained in the queue until the conversations have ended. This allows you to keep a record of all messages for auditing purposes. You can review the messages later to analyze the communication flow and troubleshoot any issues.

Compensating transactions: If an error occurs during message processing, having the messages retained in the queue allows you to perform compensating transactions. You can examine the failed messages and take appropriate actions to handle the error condition, such as resending the message or initiating compensatory actions.

By enabling retention, the messages will remain in the queue even after they have been received. They will be stored in the queue until the associated conversations are ended or until the retention period is reached (if a retention period is specified). The exact storage location depends on the configuration of the SQL Server instance and the database. The messages will be stored in the system tables related to Service Broker.

To troubleshoot Service Broker, you can use the following queries to inspect various components:

-- Message Types
SELECT *
FROM sys.service_message_types;

-- Contracts
SELECT *
FROM sys.service_contracts;

-- Queues
SELECT *
FROM sys.service_queues;

-- Services
SELECT *
FROM sys.services;

-- Endpoints
SELECT *
FROM sys.endpoints;

To check the conversation endpoints and related information, use the following query:

SELECT conversation_handle, is_initiator, s.name as 'local service',
far_service, sc.name 'contract', state_desc
FROM sys.conversation_endpoints ce
LEFT JOIN sys.services s
ON ce.service_id = s.service_id
LEFT JOIN sys.service_contracts sc
ON ce.service_contract_id = sc.service_contract_id;

To view error messages in the transmission queue, you can use:

SELECT *
FROM sys.transmission_queue;

Enabling RETENTION=ON for Service Broker queues provides benefits in terms of message retention, auditing, and compensating transactions. The messages will be stored in the queue until the conversations have ended or until the retention period is reached. The specific storage duration and location depend on the configuration of your SQL Server instance and database.