Services broker common views and scripts

Services broker common views and scripts

 -View messages in transmission queues-if you try to remove from the queue, the column will indicate where the problem is. SELECT * FROM Sys.transmission_queue  --View Service Broker activated stored procedure s Elect * from Sys.dm_broker_activated_tasks  -View each session endpoint in the database. The session endpoint represents each end of the Service Broker session. --Session Endpoint View State column shows the status of the session select * from Sys.conversation_endpoints  ---------------------------------------------- ---------------------------View active queues SELECT * from <queue name> with (NOLOCK)  --Find a database service_broker_guid SE Lect service_broker_guid from sys.databases WHERE database_id = db_id ();  -Clears session end conversation @dialog_handle with CLEANUP that cannot be completed properly;  -Specifies that the database should receive a new Broker identifier. Because the database is treated as a new Service Broker, all existing sessions in the database are immediately deleted without generating an end dialog message. ALTER DATABASE Guoqiang set New_broker  ---------------------------------------------------------------------- -----View each Service Broker network link SELECT * from sys.dm_broker_connections  -view each queue monitor in the instance, and the queue Monitor is responsible for managing the activation of the queue. SELECT * FROM sys.dm_broker_queue_monitors  -view per Service broker messageReturns a row that indicates that the instance of SQL Server is being forwarded. SELECT * FROM Sys.dm_broker_forwarded_messages  --Specifies that the database should receive a new broker identifier. Because the database is treated as a new Service Broker, all existing sessions in the database are immediately deleted without generating an end dialog message. ALTER DATABASE Guoqiang set New_broker  -Specifies that Service BROKER ALTER DATABASE Guoqiang set ENABLE_BROKER is enabled for the specified database &nbs P --Specifies to disable Service Broker ALTER DATABASE Guoqiang set Disable_broker    -----------------------------------for the specified database -------------Message type View SELECT * from Sys.service_message_types  --The Convention view in the system select * from Sys.service_contracts &nbsp ; --Routing View select * from Sys.routes  -queues are objects that are part of a schema. Queue displayed in catalog view select * FROM sys.objects  --Service View SELECT * FROM Sys.Services  --relationship view of message and convention SELECT * FROM Sys.servi Ce_contract_message_usages  --View the list of message types, the conventions they use SELECT  c.name as contract, M.name as MessageType,  case & nbsp;   when is_sent_by_initiator = 1      and is_sent_by_target    = 1 THEN ' any '     when is_sent_by_initiator = 1 THEN ' initiator '     when is_sent_by_target    = 1 THEN ' target '  end As Sentby from Sys.service_message_types as M  join sys.service_contract_message_usages as U     on M . message_type_id = u.message_type_id  join sys.service_contracts as C     on c.service_contract_id = u.service_contract_id ORDER by C.name, M.name;  --View the name of the internal table used by the queue SELECT Q.name as QueueName, i.name as InternalName from Sys.service_queues as Q  join sys.int Ernal_tables as I     on q.object_id = i.parent_object_id;      -View services and Queues SELECT s.name, q.name from sys.services as S  join sys.service_queues as Q &nbsp ;  on s.service_queue_id = q.object_id;  ----mapping of services and conventions sys.service_contract_usages   SELECT s.name as [service], q.name as [Queue], c.name as [contra CT] from sys.services as S  join sys.service_queues as Q     on s.service_queue_id= q.object_id  join sys.service_contract_usages as U     on s.service_id = u.service_id   JOIN sys.service_contracts as C     on u.service_contract_id = c.service_contract_id;  -Clears the session in error state DECLARE @handle as uniqueidentifier; DECLARE Conv CURSOR for  select conversation_handle  from sys.conversation_endpoints The state = ' ER '; OPEN Conv; FETCH NEXT from Conv into @handle; While @ @FETCH_STATUS = 0 BEGIN  end conversation @handle with CLEANUP;  fetch NEXT from Conv into @handle; End Close Conv; DEALLOCATE Conv;