Snippet Name: envqueue
Tags: helpful,scriptgeneration
Created Date: Oct 1 2021 8:05AM
Last Modified Date: Apr 1 2022 11:23AM
–desc: service broker queue information
Dependencies: none-standalone code
default description
Sample Output:
--desc: service broker queue information
SELECT * ,CASE WHEN RW > 1 THEN 'ALTER DATABASE ' + QUOTENAME(MyAlias.DatabaseName) + ' SET NEW_BROKER WITH ROLLBACK IMMEDIATE' ELSE '' END AS DuplicateServiceBrokerFix
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY service_broker_guid ORDER BY name) AS RW,
name AS DatabaseName,
service_broker_guid
FROM sys.databases
WHERE [database_id] > 4
) MyAlias
WHERE MyAlias.RW > 1
SELECT DB_NAME([database_id]) AS DatabaseName,
[database_id] ,
is_broker_enabled ,
CASE WHEN [is_broker_enabled] = 0 THEN 'ALTER DATABASE ' + QUOTENAME(name) + ' SET ENABLE_BROKER;' ELSE '' END AS EnableCommand,
'ALTER DATABASE ' + QUOTENAME(name) + ' SET DISABLE_BROKER;ALTER DATABASE ' + QUOTENAME(name) + ' SET ENABLE_BROKER;' AS ToggleCommand
FROM sys.databases WHERE name= DB_NAME()
select 'All rows in the queue are errors' AS Note,
MAX(CONVERT(VARCHAR(256),[transmission_status])) + '...' AS TransmissionStatus,
'No rows should exist in the queue, all awaiting processing.' AS Expectation,
COUNT(*) AS NumberofItemsInTheTransmissionQueue,
'
DECLARE @conversation UNIQUEIDENTIFIER;
WHILE EXISTS (SELECT 1 FROM [sys].[transmission_queue] )
BEGIN
SET @conversation = (SELECT TOP 1 [conversation_handle] FROM [sys].[transmission_queue] );
END CONVERSATION @conversation WITH CLEANUP;
END;
'
AS DrainCommand ,
MAX(CONVERT(VARCHAR(100),[message_body])) + '...' AS MessageHeader
--select *
FROM sys.transmission_queue ;
SELECT COUNT(conversation_handle) AS HandleCount , 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
GROUP BY is_initiator, s.name ,
far_service, sc.name , state_desc
SELECT db_name([mon].[database_id]) AS DatabaseName,[database_id] ,
[queues].[name] ,
[mon].[queue_id] ,
[mon].[state] ,
[mon].[last_empty_rowset_time] ,
[mon].[last_activated_time] ,
[mon].[tasks_waiting]
FROM sys.dm_broker_queue_monitors [mon]
INNER JOIN sys.[service_queues] [queues] ON [queues].[object_id] = [mon].[queue_id]
SELECT [queues].[name] ,
CASE WHEN [mon].is_receive_enabled = 1 AND [mon].[is_enqueue_enabled] = 1 THEN ''
ELSE 'ALTER QUEUE ' + QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) + ' WITH STATUS = ON'
END AS ClearQueueCommand,
[parti].[rows] AS CurrentRows,
[mon].[name] ,
[mon].[type] ,
[mon].[type_desc] ,
[mon].[create_date] ,
[mon].[modify_date] ,
[mon].[max_readers] ,
[mon].[activation_procedure] ,
[mon].[execute_as_principal_id] ,
USER_NAME([mon].[execute_as_principal_id]) AS ExecutePrincipalName,
[mon].[is_activation_enabled] ,
[mon].[is_receive_enabled] ,
[mon].[is_enqueue_enabled] ,
[mon].[is_retention_enabled] ,
[mon].[is_poison_message_handling_enabled],
QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) AS QualifiedName ,
SCHEMA_NAME([queues].[schema_id]) AS SchemaName ,
[queues].[name] AS ServiceBrokerName ,
' SELECT CONVERT(VARCHAR(MAX),message_body) AS msg,''' + [queues].[name] + ''' AS ServiceBrokerQueueName, * FROM '
+ QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) + ';' AS cmd ,
ClearQueueCommand = 'WHILE EXISTS( SELECT ''' + [queues].[name] + ''' AS ServiceBrokerQueueName, * FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) + ')
BEGIN
waitfor (
receive top(1)
conversation_group_id
from ' + QUOTENAME([queues].[name]) + '
), timeout 1000;
END'
FROM [sys].[objects] AS objz
INNER JOIN [sys].[partitions] AS parti ON [parti].[object_id] = objz.[object_id]
INNER JOIN [sys].[objects] AS queues ON objz.[parent_object_id] = [queues].[object_id]
INNER JOIN sys.[service_queues] [mon] ON [queues].[object_id] = [mon].[object_id]
WHERE [queues].[type_desc] = 'SERVICE_QUEUE'
AND [parti].[index_id] = 1
SELECT 'If the values are other than 00000000-0000-0000-0000-000000000000 it indicates encryption is being used:
' AS Notes,COUNT(*) AS endpointCount,MIN(inbound_session_key_identifier) AS inbound_session_key_identifier, MAX(outbound_session_key_identifier) AS outbound_session_key_identifier
FROM sys.conversation_endpoints