I have some questions about SQL Server 2008 Broker Service. Here is the context:

SQL Server 2008 (replicated tables) — Broker Service — A DLL 3rd party — Video Application

When some values are updated / inserted in the tables after replication, the service broker informs a 3rd DLL party in order to recover the different messages in the appropriated queues and then this DLL writes information in the application. But sometime, some messages are disappeared. So:

– if I activate the retention setting on broker service, can I store the messages ? how many time ? and can I export them (if possible) ? Is it possible to do a replay of these messages ?

– where can I look for the service broker ? (system tables, system views or in the application DB ?)

– All the SQL Server broker service activities are cetainly logged. But, where ?

The SQL Server Developer team creates contracts and services for the broker, but they are not visible in SSMS. Is this a display bug or not ?

Thanks for advance for your ideas / help – Regards – Have a nice day ! RHUM2

The more information you can provide about your architecture the better, not sure I understand your setup. I will try to answer some of these.

We only recommend using retention to troubleshoot and not to run permenantly in production. The messages will stay in the queue until the conversation is ended. It will then be removed. Instead of retention some will insert the message and other details in a audit table for tracking. You can not replay the message but when you receive the message from the queue you do have the option to resend the same message to a queue. You would have to code this in your activation stored procedure or your external application that is receiving the messages from the queue.

RETENTION (create queue command in books online)

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.

Service broker has several system tables and views that you can use:

Two of the most important are nversation_endpoint and sys.transmission_queue. Conversation_endpoint lists metadata about each conversation. Transmission_queue holds metadata on messges sent to the target. When you send a message it adds a record to the sys.transmission_queue. When the target recevices the message in the queue and ACK is sent back to the initiator. When the ACK is received the message will be removed from sys.transmission_queue. If not service broker will try to resend the message.

Some other helpful service broker tables and view. See books online for complete list.

select * from sys.dm_broker_activated_tasksselect * from sys.dm_broker_connectionsselect * from sys.dm_broker_queue_monitorsselect * from sys.service_broker_endpointsselect * from sys.service_queuesselect * from sys.routes

Service broker activity is not logged but you can certainly add logging to your activation stored procedure or application to log activity. Profiler is a good tool and has several broker events to help troubleshoot. Also two places to check for errors are your sql server error logs and the transmission_status column in sys.transmission_queue.

SQL Server Management Studio does expose service broker information. Under a database you will see a service broker folder. Expand this and you will see more for folders for message types, contracts, queues, services, routes. If you do not see them then you will need to check you permissions.

Some of the questions I wouldask is how do you know that messages disappear? How are the messages sent to the queue? What is receving the messages out of the queue, an activation stored procedure or a external application? If the messages are being sent by a trigger on a replicated table, you might be able to add code to audit or track the send side. You might also run a profiler trace to see activity. I would also check your sys.transmission_queue (transmission_status) and conversation_endpoints tables.

Here is a script that you can use to gather information on service broker objects, etc. Best to save the output as text (.rpt).

print ***sys.databases***select * from master.sys.databases where is_broker_enabled = 1 and name not in(tempdb, model, AdventureWorks, AdventureWorksDW)print ***sys.dm_broker_activated_tasks***select * from sys.dm_broker_activated_tasksprint ***sys.dm_broker_connections***select * from sys.dm_broker_connectionsprint ***sys.dm_broker_forwarded_messages***select * from sys.dm_broker_forwarded_messagesprint ***sys.dm_broker_queue_monitors***select * from sys.dm_broker_queue_monitorsprint ***sys.service_broker_endpoints***select * from sys.service_broker_endpointsprint ***sys.tcp_endpoints***select * from sys.tcp_endpointsprint ***sys.certificates***select * from sys.certificatesprint ***sys.database_mirroring***select * from sys.database_mirroring where mirroring_guid is not nullprint ***sys.dm_db_mirroring_connections***select * from sys.dm_db_mirroring_connectionsprint ***sys.dm_os_memory_clerks (broker)***select * from sys.dm_os_memory_clerks where type like %BROKER% order by type desc

DECLARE tnames_cursor CURSORFOR select name from master.sys.databases where is_broker_enabled = 1 and name not in(tempdb, model, AdventureWorks, AdventureWorksDW)OPEN tnames_cursor;DECLARE @dbname sysname;FETCH NEXT FROM tnames_cursor INTO @dbname;WHILE (@@FETCH_STATUS = 0)BEGINselect @dbname = RTRIM(@dbname);select @dbname;EXEC (USE @dbname);printprint *** @dbname ***print ***sys.service_message_types***EXEC (SELECT * FROM @dbname .sys.service_message_types);print ***sys.service_contract_message_usages***EXEC (SELECT * FROM @dbname .sys.service_contract_message_usages);print ***sys.service_contracts***EXEC (SELECT * FROM @dbname .sys.service_contracts);print ***sys.service_contract_usages***EXEC (SELECT * FROM @dbname .sys.service_contract_usages);print ***sys.service_queues***EXEC (SELECT * FROM @dbname .sys.service_queues);print ***sys.service_queue_usages***EXEC (SELECT * FROM @dbname .sys.service_queue_usages);print ***sys.services***EXEC (SELECT * FROM @dbname .sys.services);print ***sys.routes***EXEC (SELECT * FROM @dbname .sys.routes);print ***sys.remote_service_bindings***EXEC (SELECT * FROM @dbname .sys.remote_service_bindings);print ***sys.certificates***EXEC (SELECT * FROM @dbname .sys.certificates);print ***sys.dm_qn_subscriptions***EXEC (SELECT * FROM @dbname .sys.dm_qn_subscriptions);print ***sys.transmission_queue (count and top 1500)***EXEC (SELECT count(*) as count FROM @dbname .sys.transmission_queue);EXEC (SELECT top 1500 * FROM @dbname .sys.transmission_queue);print nversation_endpoints (count and top 1500)***EXEC (SELECT count(*) as count FROM @dbname .sys.conversation_endpoints);EXEC (SELECT top 1500 * FROM @dbname .sys.conversation_endpoints);FETCH NEXT FROM tnames_cursor INTO @dbname;END;CLOSE tnames_cursor;DEALLOCATE tnames_cursor;

I would like to involve someone familiar with this issue to have a look, and give an update as soon as possible. Thanks for your understanding.

The more information you can provide about your architecture the better, not sure I understand your setup. I will try to answer some of these.

We only recommend using retention to troubleshoot and not to run permenantly in production. The messages will stay in the queue until the conversation is ended. It will then be removed. Instead of retention some will insert the message and other details in a audit table for tracking. You can not replay the message but when you receive the message from the queue you do have the option to resend the same message to a queue. You would have to code this in your activation stored procedure or your external application that is receiving the messages from the queue.

RETENTION (create queue command in books online)

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.

Service broker has several system tables and views that you can use:

Two of the most important are nversation_endpoint and sys.transmission_queue. Conversation_endpoint lists metadata about each conversation. Transmission_queue holds metadata on messges sent to the target. When you send a message it adds a record to the sys.transmission_queue. When the target recevices the message in the queue and ACK is sent back to the initiator. When the ACK is received the message will be removed from sys.transmission_queue. If not service broker will try to resend the message.

Some other helpful service broker tables and view. See books online for complete list.

select * from sys.dm_broker_activated_tasksselect * from sys.dm_broker_connectionsselect * from sys.dm_broker_queue_monitorsselect * from sys.service_broker_endpointsselect * from sys.service_queuesselect * from sys.routes

Service broker activity is not logged but you can certainly add logging to your activation stored procedure or application to log activity. Profiler is a good tool and has several broker events to help troubleshoot. Also two places to check for errors are your sql server error logs and the transmission_status column in sys.transmission_queue.

SQL Server Management Studio does expose service broker information. Under a database you will see a service broker folder. Expand this and you will see more for folders for message types, contracts, queues, services, routes. If you do not see them then you will need to check you permissions.

Some of the questions I wouldask is how do you know that messages disappear? How are the messages sent to the queue? What is receving the messages out of the queue, an activation stored procedure or a external application? If the messages are being sent by a trigger on a replicated table, you might be able to add code to audit or track the send side. You might also run a profiler trace to see activity. I would also check your sys.transmission_queue (transmission_status) and conversation_endpoints tables.

Here is a script that you can use to gather information on service broker objects, etc. Best to save the output as text (.rpt).

print ***sys.databases***select * from master.sys.databases where is_broker_enabled = 1 and name not in(tempdb, model, AdventureWorks, AdventureWorksDW)print ***sys.dm_broker_activated_tasks***select * from sys.dm_broker_activated_tasksprint ***sys.dm_broker_connections***select * from sys.dm_broker_connectionsprint ***sys.dm_broker_forwarded_messages***select * from sys.dm_broker_forwarded_messagesprint ***sys.dm_broker_queue_monitors***select * from sys.dm_broker_queue_monitorsprint ***sys.service_broker_endpoints***select * from sys.service_broker_endpointsprint ***sys.tcp_endpoints***select * from sys.tcp_endpointsprint ***sys.certificates***select * from sys.certificatesprint ***sys.database_mirroring***select * from sys.database_mirroring where mirroring_guid is not nullprint ***sys.dm_db_mirroring_connections***select * from sys.dm_db_mirroring_connectionsprint ***sys.dm_os_memory_clerks (broker)***select * from sys.dm_os_memory_clerks where type like %BROKER% order by type desc

DECLARE tnames_cursor CURSORFOR select name from master.sys.databases where is_broker_enabled = 1 and name not in(tempdb, model, AdventureWorks, AdventureWorksDW)OPEN tnames_cursor;DECLARE @dbname sysname;FETCH NEXT FROM tnames_cursor INTO @dbname;WHILE (@@FETCH_STATUS = 0)BEGINselect @dbname = RTRIM(@dbname);select @dbname;EXEC (USE @dbname);printprint *** @dbname ***print ***sys.service_message_types***EXEC (SELECT * FROM @dbname .sys.service_message_types);print ***sys.service_contract_message_usages***EXEC (SELECT * FROM @dbname .sys.service_contract_message_usages);print ***sys.service_contracts***EXEC (SELECT * FROM @dbname .sys.service_contracts);print ***sys.service_contract_usages***EXEC (SELECT * FROM @dbname .sys.service_contract_usages);print ***sys.service_queues***EXEC (SELECT * FROM @dbname .sys.service_queues);print ***sys.service_queue_usages***EXEC (SELECT * FROM @dbname .sys.service_queue_usages);print ***sys.services***EXEC (SELECT * FROM @dbname .sys.services);print ***sys.routes***EXEC (SELECT * FROM @dbname .sys.routes);print ***sys.remote_service_bindings***EXEC (SELECT * FROM @dbname .sys.remote_service_bindings);print ***sys.certificates***EXEC (SELECT * FROM @dbname .sys.certificates);print ***sys.dm_qn_subscriptions***EXEC (SELECT * FROM @dbname .sys.dm_qn_subscriptions);print ***sys.transmission_queue (count and top 1500)***EXEC (SELECT count(*) as count FROM @dbname .sys.transmission_queue);EXEC (SELECT top 1500 * FROM @dbname .sys.transmission_queue);print nversation_endpoints (count and top 1500)***EXEC (SELECT count(*) as count FROM @dbname .sys.conversation_endpoints);EXEC (SELECT top 1500 * FROM @dbname .sys.conversation_endpoints);FETCH NEXT FROM tnames_cursor INTO @dbname;END;CLOSE tnames_cursor;DEALLOCATE tnames_cursor;

I have looked at the code and I think it is possible to add much checks or controls to provide a better log activity. I will examine and study your different links. I did not know this SQL Server Brokerss blog team.

I have all Sysadmin rights and in SSMS, I dont always see contracts and services (note that those will be created directly in SQL code).

Thanks for advance for your ideas / help – Regards – Have a nice day ! RHUM2