Service Broker

Deactivate Queue

Use this section to DE-ACTIVATE the key service broker queues once issue has been identified


    ALTER QUEUE [QueueName] WITH ACTIVATION ( STATUS = OFF ) 

Reactivate Queue

Use this section to RE-ACTIVATE the key service broker queues once issue has resolved


ALTER QUEUE [QueueName] WITH ACTIVATION ( STATUS = ON )  

Disable Broker

ALTER DATABASE [DBName]  
SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE 

Enable Broker

ALTER DATABASE PARIS 
SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE 

Clear down Queues

DECLARE @handle UNIQUEIDENTIFIER; 
WHILE (SELECT COUNT(*) FROM [TargetQueueName]) > 0 
BEGIN 
RECEIVE TOP (1) @handle = conversation_handle FROM [TargetQueueName]; 
END CONVERSATION @handle WITH CLEANUP 
END 

Change number of threads

ALTER QUEUE [dbo].[QueueName] 
WITH STATUS = ON , RETENTION = OFF , 
ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[ActivationProc] , MAX_QUEUE_READERS = 16 , EXECUTE AS N'dbo'  ), 
POISON_MESSAGE_HANDLING (STATUS = OFF) 

DMV - Status of Queues

select name, type_desc, activation_procedure, is_activation_enabled, is_receive_enabled, is_enqueue_enabled, is_retention_enabled, is_poison_message_handling_enabled
from sys.service_queues
where 1=1
--AND name = N'MyQueue';
--AND is_activation_enabled = 
ORDER BY 1

>