I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer. When I setup event notification on a queue, it creates a queue monitor and if receiving a message from that queue causes a poison message the queue monitor disappears. It's not in "RECEIVE" or "INACTIVE" it's just drops. And after enabling the queue with ALTER QUEUE somequeue WITH STATUS = ON; the queue monitor does not re-appear and and I need to drop and recreate event notification in order to have event notification working again. So the question is, is that by design or not? And after I re-enable the queue after poison message is it a common practice to recreate event notifications. Thanks!

Previously I asked about latch contention / locks which can happen during Service Broker work: https://dba.stackexchange.com/questions/271605/service-broker-locks . I got a great answer on general question but I still have narrowly focused question. MSDN ( SEND ): > Messages that are sent to services in other instances of the SQL Server Database Engine are stored in a transmission queue in the current database until they can be transmitted to the service queues in the remote instances. Messages sent to services in the same instance of the Database Engine are put directly into the queues associated with these services. Question: Is there a way to force a message to be put in a local transmission_queue even if services are located on the same Database Engine? It could be preferred solution in my circumstances where I have sufficient hardware resources but sometimes I get latch contention. Why it can help is described in the link above ---------- Test after David's answer . Preparation:

-- Source DB
use master
go
drop database [TestSendMessage]
go
create database [TestSendMessage]
go
alter database [TestSendMessage] set enable_broker with rollback immediate;
go
use [TestSendMessage]
go
create message type datachanges_messagetype 
  validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.TestSendMessage_initiatorqueue
  with status = on
     , retention = off
	, poison_message_handling ( status = on )
  on [default];
go
create service TestSendMessage_initiatorservice
  on queue TestSendMessage_initiatorqueue
  ( datachanges_contract );
go
DROP ROUTE [AutoCreatedLocal]
go
CREATE ROUTE Forwarder
    WITH  
    ADDRESS = 'TCP://localhost:4022' ;   
go
grant send on service::[TestSendMessage_initiatorservice] to public;
go

-- Target DB
use master
go
drop database [TestReceiveMessage]
go
create database [TestReceiveMessage]
go
alter database [TestReceiveMessage] set enable_broker with rollback immediate;
go
use [TestReceiveMessage]
go
create message type datachanges_messagetype 
  validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.TestReceiveMessage_destinationqueue
  with status = on
     , retention = off
	, poison_message_handling ( status = on )
  on [default];
go
create service TestReceiveMessage_destinationservice
  on queue TestReceiveMessage_destinationqueue
  ( datachanges_contract );
go
DROP ROUTE [AutoCreatedLocal]
go
CREATE ROUTE Forwarder
    WITH  
    ADDRESS = 'TCP://localhost:4022' ;   
go
grant send on service::[TestReceiveMessage_destinationservice] to public;
go

-- endpoint
if not exists ( select * from sys.endpoints where name = 'ServiceBrokerEndpoint' )
create endpoint ServiceBrokerEndpoint
  state = started
  as tcp ( listener_ip = (127.0.0.1), listener_port = 4022)
  for service_broker();
go

-- Tests
use [TestSendMessage]
go

declare @handle  uniqueidentifier = null

begin dialog conversation @handle 
					from service TestSendMessage_initiatorservice
					to   service 'TestReceiveMessage_destinationservice'
					  on contract datachanges_contract
				    with encryption = off;

select @handle; -- 0EC9CCEE-13D3-EA11-B4E1-983B8F11A147
;send on conversation '0EC9CCEE-13D3-EA11-B4E1-983B8F11A147' message type datachanges_messagetype( '[{aaa:1}]' );
select * from sys.transmission_queue

And yeah I see that local sys.transmission_queue is used using query and XE Trace: BUT! Some time later ( maybe several seconds, I don't know exactly ) if I send several messages using the conversation, I do not see messages in sys.transmission_queue and that's what's in the XE: IF I create a new conversation or new_broker I'll get the same situation: sys.transmission_queue usage at the beginning and then - no

select @@version

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) 
	Mar 18 2018 09:11:49 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 18363: ) (Hypervisor)

I have C# application that uses SQLDependency to get updates from a message table. I have tested it quite extensively and worked great in the development environment but when I ran it on the production server the server crashed. I can not be absolutely sure that it is this because we picked it up ~10 minutes after I ran the application. From what I understand from how the SQLDependency works it uses stored procedures to interact with the service broker. The errors I received > *SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.* > > *Could not find an entry for table or index with partition ID 428869310480384 in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.* We are running SQL Server 2005 SP1. Edit:(removed code the link explains it better) I followed this example and mainly changed the query and what I do with the data. http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/

I have the following SQL service broker set up:

USE  master
-- Create database
ALTER DATABASE ServiceBrokerDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ServiceBrokerDemo
CREATE DATABASE ServiceBrokerDemo;

-- Create a log
USE ServiceBrokerDemo
GO 
DROP TABLE IF EXISTS DemoServiceBrokerLog;
CREATE TABLE DemoServiceBrokerLog (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Date DATETIME,
	MessageTypeName sysname,
	ActivationFunction sysname, 
	MessageBody xml
);

-- CREATE TWO MESSAGE QUEUES: one for the sender and one for the receiver (who processes messages from the sender)
USE ServiceBrokerDemo
GO 

CREATE QUEUE dbo.InitiatorQueue WITH STATUS=ON ON [PRIMARY]
GO

CREATE QUEUE dbo.TargetQueue WITH STATUS=ON ON [PRIMARY]
GO


-- CREATE TWO ACTIVATION FUNCTIONS: for the two queues
-- This is for the target queue
CREATE OR ALTER PROCEDURE [dbo].[TargetQueueProcess]
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @conversationHandle UNIQUEIDENTIFIER
	DECLARE @messagetypename NVARCHAR(256)
	DECLARE @messagebody XML

	DECLARE @errorNumber AS int;
	DECLARE @errorMessage AS nvarchar(3000);

	WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue)
	BEGIN
		;RECEIVE TOP(1) @conversationHandle = conversation_handle
			 , @messagetypename = message_type_name
			 , @messagebody = CASE WHEN validation = 'X' THEN CAST(message_body AS XML) ELSE NULL END
		  FROM [dbo].[TargetQueue]

		IF @conversationHandle IS NOT NULL
		BEGIN
			-- Log
			INSERT INTO DemoServiceBrokerLog VALUES (
						getdate(),
						@messageTypeName,
						'TargetQueueProcess', 
						@messagebody
				);


			IF @messageTypeName = 'http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation ' 
			BEGIN
				-- End the conversation 
				-- The initiator will receive an EndDialog message afterwards
				END CONVERSATION @conversationHandle;
			END
			ELSE IF @messageTypeName = 'http://my.dba.aventure/dbo/DemoServiceBroker/message ' 
			BEGIN
				BEGIN TRY 
					-- Process the message
					PRINT ('Do the actual task in here')
				END TRY 
				BEGIN CATCH 
					-- Capture the error
					SELECT @errorNumber = ERROR_NUMBER()
					, @errorMessage = ERROR_MESSAGE()

					-- End the conversation with an error
					-- The initiator will receive an Error message afterwards
					END CONVERSATION @conversationHandle WITH ERROR = @errorNumber DESCRIPTION = @errorMessage;
				END CATCH 
			END
		END
	END
END
GO

-- This procedure mainly to end the conversation at the initiator side and perform action upon errors
USE ServiceBrokerDemo
GO
CREATE OR ALTER PROCEDURE [dbo].[InitiatorQueueProcess]
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @conversationHandle UNIQUEIDENTIFIER
	DECLARE @messageTypeName SYSNAME
	DECLARE @messagebody XML

	DECLARE @errorCode int;
	DECLARE @errorMessage nvarchar(3000);

	--BEGIN TRANSACTION
		;RECEIVE TOP(1) @conversationHandle = conversation_handle
			 , @messageTypeName = message_type_name
			 , @messagebody = CASE WHEN validation = 'X' THEN CAST(message_body AS XML) ELSE NULL END
		  FROM [dbo].[InitiatorQueue]

		IF @conversationHandle IS NOT NULL
		BEGIN
			INSERT INTO DemoServiceBrokerLog VALUES (
						getdate(),
						@messageTypeName,
						'InitiatorQueueProcess', 
						@messagebody
			)

			IF @messageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer ' 
			BEGIN
				;SEND ON CONVERSATION @conversationHandle 
					MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation] ;
			END
			ELSE IF @messageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog ' 
			BEGIN
				END CONVERSATION @conversationHandle;
			END
			ELSE IF @messageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error '
			BEGIN 
				-- Log the error before ending the conversation
				SET @errorCode = 
				(SELECT @messagebody.value(
				N'declare namespace 
				brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error "; 
				(/brokerns:Error/brokerns:Code)', 'int'));

				SET @errorMessage = (SELECT @messagebody.value(
				'declare namespace
				brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error ";
				(/brokerns:Error/brokerns:Description)', 'nvarchar(3000)'));

				-- do something with the error 
				PRINT('log the errors to some table')

				-- End conversation on the initiator side
				END CONVERSATION @conversationHandle;	
			END
		END
	--COMMIT TRANSACTION;
END
GO


-- Alter queue to associate them to the activation functions
USE ServiceBrokerDemo
GO
ALTER QUEUE dbo.TargetQueue WITH STATUS = ON, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.TargetQueueProcess, MAX_QUEUE_READERS = 1, EXECUTE AS SELF) , POISON_MESSAGE_HANDLING (STATUS = OFF) 
GO 
ALTER QUEUE dbo.InitiatorQueue WITH STATUS = ON, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.InitiatorQueueProcess, MAX_QUEUE_READERS = 1, EXECUTE AS SELF) , POISON_MESSAGE_HANDLING (STATUS = OFF) 
GO 

-- Create message type 
CREATE MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/message] 
CREATE MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation] 

-- Create contract
CREATE CONTRACT [http://my.dba.aventure/dbo/DemoServiceBroker/contract]  
(
	[http://my.dba.aventure/dbo/DemoServiceBroker/message]  SENT BY INITIATOR, 
	[http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation]  SENT BY INITIATOR
)

-- CREATE Service
CREATE SERVICE [http://my.dba.aventure/dbo/DemoServiceBroker/InitiatorService]   ON QUEUE [dbo].[InitiatorQueue] ([http://my.dba.aventure/dbo/DemoServiceBroker/contract]) 
CREATE SERVICE [http://my.dba.aventure/dbo/DemoServiceBroker/TargetService]   ON QUEUE [dbo].[TargetQueue] ([http://my.dba.aventure/dbo/DemoServiceBroker/contract]) 

I sent a message from the initiator to the target using the following code:

USE  ServiceBrokerDemo

DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @fromService SYSNAME = 'http://my.dba.aventure/dbo/DemoServiceBroker/InitiatorService '
DECLARE @toService SYSNAME = 'http://my.dba.aventure/dbo/DemoServiceBroker/TargetService '
DECLARE @contract SYSNAME = 'http://my.dba.aventure/dbo/DemoServiceBroker/contract '

-- Craft a contrived message body
DECLARE @messageBody XML = 'some message'


BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE @fromService
TO SERVICE @toService
ON CONTRACT @contract
WITH ENCRYPTION = OFF;

BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = 5

;SEND ON CONVERSATION @conversationHandle
	MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/message]  
	(@messageBody)

I ran the following code to check the status of the queue

SELECT *
FROM sys.dm_broker_queue_monitors AS m
INNER JOIN sys.service_queues q
ON m.queue_id = q.object_id

The status of the queue keeps to be RECEIVES_OCCURRING even hours after the last time I ran the code that sends a message. I would expect the state to be INACTIVE since all the messages in the target already processed right? And the target activation function keeps executing even though nothing in the queue. However, as soon as I fixed the while loop part, the state of the queue is correctly changed to INACTIVE as soon the messages have been processed.

...
	WHILE (1=1)
	BEGIN
		;RECEIVE TOP(1) @conversationHandle = conversation_handle
			 , @messagetypename = message_type_name
			 , @messagebody = CASE WHEN validation = 'X' THEN CAST(message_body AS XML) ELSE NULL END
		  FROM [dbo].[TargetQueue]
		
		IF (@@ROWCOUNT=0)
		BEGIN
			BREAK
		END

		IF @conversationHandle IS NOT NULL
		BEGIN
...

Could you help me explain why the WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue) caused the service broker keep calling the activation function to receive messages even though there is no message on the target queue?

I've inherited a server where all databases have the Service Broker enabled. I suspect that only SSRS uses it. Given a database where the Service Broker is enabled, what signs indicate that it is actually being used? I know that we cannot prove that it isn't used, but I'd like to know what can prove that it is. I have consulted the documentation, but it appears that Service Broker has multiple usages and I would have to check each one. I am hoping that a much simpler solution exists. I have Query Store enabled on all databases and I am willing to add more monitoring if it will solve this.

We have an SQL Server 2022 CU13 Enterprise on a Windows 2022 VM with 8 vCPU. The sqlserver.exe process shows a high CPU utilization between 10-15% even when there is no user generated load (meaning there is no user connection at the moment, even the SQL Server Agent is stopped to eliminate all non-system load) on the server. The dominant wait type is SOS_SCHEDULER_YELD at that idle time. This server activity also blocks the SQL Server service to be stopped. An attempt to stop the service results in an endless waiting and the Windows service process needs to be killed so that it "stops". After the restart the utilization is perfectly fine for some time (the idle server has */- 0 cpu utilization), but after some period of time (a few hours or a few days maximum) with the user load running the issue is back again. Meaning the utilization is higher than it should be with the user load generated and again, after the load is stopped (no user connections) the utilization remains high as mentioned above... There are no suspicious messages in the error log, XE health session, Windows event log... Here is the view to the Activity Monitor on the server in during the above described idle period: After some investigation I likely identified the SPID, that causes this utilization. It seems to be a system SPID (SPID 37 at the time of the investigation) and it consumes about 10 seconds of cpu time over 10 seconds period (see the output from the Query 4 below). The database_id for the SPID seems to be changing between master (database_id = 1) and one of the user databases (database_id = 10). Only databases id 1 and 10 seems to be involved in this SPID activity. The SPID is also generating an ever increasing allocation in the tempdb (see the output from the Query 3 below). Here is the output from the sys.dm_exec_request at the same time as the Activity Monitor picture above has been taken (the problematic spid is the 37): Here is the output from sp_WhoIsActive at the same time: Here is a calculation of consumed cpu time for the SPID over the 10 seconds period: As I understand it, it seems to be a Service Broker activity. However, there is neither Service Broker enabled in the master nor in the user database. We do not actively use the Service Broker in our databases. I have no idea, what this broker activity could be about and with what system activity it could be connected. There is no any "unusual" feature used either on the server or in that user database, just a "basic common features set", the only "unusual feature" is the TDE enabled. Any idea about how I can further investigate the root cause for this issue (what is this broker activity about) and how I can get rid of it?

We have a Service Broker that listens for changes to the database and then sends messages to a queue based on the changed data. This works if the logged in user is has a server role of sysadmin or db_owner. We want this to work for other users as well but do not want them to have sysadmin or db_owner level permissions. I created a new role but I am unsure what permissions I need to grant to allow the service broker to work. Any thoughts? I created a new role and gave my test user membership in that role. I have granted the role receive on the queues and send on the services. such as:

GRANT RECEIVE ON [dbo].[QueryNotificationErrorsQueue] to [db_servicebrokeruser]
GRANT RECEIVE ON [dbo].[EventNotificationErrorsQueue] to [db_servicebrokeruser]
GRANT RECEIVE ON [dbo].[ServiceBrokerQueue] to [db_servicebrokeruser]

GRANT SEND ON SERVICE:: [http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService]  to [db_servicebrokeruser]
GRANT SEND ON SERVICE:: [http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker]  to [db_servicebrokeruser]
GRANT SEND ON SERVICE:: [http://schemas.microsoft.com/SQL/Notifications/EventNotificationService]  to [db_servicebrokeruser]

I still don't see the messages flowing to the queue and I don't see any errors either.

I need to use Service Broker in order to run several stored procedures parallelly (instead of sequentially). Please be aware that I need to use Service Broker, not alternative solutions such as SSIS or SQL Agent Jobs. I use SQL Server 13.0 (2016), SSMS 19.0 as well as AdventureWorksDW2016 as a database. Unfortunately, the very few implementations I could find online do not work for me. Also I have tried writing a script myself. I do not really get any issues, however, I have provided a table to store the starttime and endtime of my procedures, and unfortunately when I execute the procedures, I can see that they are being executing after the procedure before is finished. My script looks like so: Do you see what I am doing wrong or do you perhaps have a good implementation online? The ones that I have found, did not really help me. -- enable Service Broker; might be enabled by default --alter database [AdventureWorksDW2016_2] set enable_broker with rollback immediate; --Create a Service Broker queue and service for the stored procedure: CREATE QUEUE parallel_proc_queue; CREATE SERVICE parallel_proc_service ON QUEUE parallel_proc_queue; --Create a stored procedure that sends a message to the Service Broker queue and executes the desired stored procedure CREATE PROCEDURE send_parallel_proc_message @proc_name VARCHAR(100) AS BEGIN DECLARE @conversation_handle UNIQUEIDENTIFIER BEGIN DIALOG CONVERSATION @conversation_handle FROM SERVICE parallel_proc_service TO SERVICE 'parallel_proc_service' ON CONTRACT parallel_proc_contract WITH ENCRYPTION = OFF; SEND ON CONVERSATION @conversation_handle MESSAGE TYPE parallel_proc_message (@proc_name); EXECUTE @proc_name; END CONVERSATION @conversation_handle; END --Create a stored procedure that receives messages from the Service Broker queue and executes the stored procedure specified in the message: CREATE PROCEDURE parallel_proc_receiver AS BEGIN DECLARE @message_body VARCHAR(100) DECLARE @conversation_handle UNIQUEIDENTIFIER WHILE (1=1) BEGIN WAITFOR ( RECEIVE TOP(1) @message_body = message_body, @conversation_handle = conversation_handle FROM parallel_proc_queue ), TIMEOUT 1000; IF (@@ROWCOUNT = 0) BREAK; EXECUTE @message_body; END CONVERSATION @conversation_handle; END END -- define the contract for the Service Broker conversation CREATE MESSAGE TYPE parallel_proc_message VALIDATION = WELL_FORMED_XML; CREATE CONTRACT parallel_proc_contract (parallel_proc_message SENT BY ANY); --Start the Service Broker conversation DECLARE @conversation_handle UNIQUEIDENTIFIER BEGIN DIALOG CONVERSATION @conversation_handle FROM SERVICE parallel_proc_service TO SERVICE 'parallel_proc_service' ON CONTRACT parallel_proc_contract WITH ENCRYPTION = OFF BEGIN CONVERSATION TIMER (@conversation_handle) TIMEOUT = 180000; -- 3 minutes --create table to measure time (to check if parallellity actually works) CREATE TABLE dbo.StoredProcedureLog ( SPName NVARCHAR(255), StartTime DATETIME, EndTime DATETIME ); --define stored procedures CREATE PROCEDURE usp_GetSalesForCustomer AS BEGIN WAITFOR DELAY '00:00:02'; -- Log the start time of the stored procedure INSERT INTO dbo.StoredProcedureLog (SPName, StartTime) VALUES ('usp_GetSalesForCustomer', GETDATE()); SELECT c.FirstName, c.LastName, SUM(f.SalesAmount) AS TotalSales FROM dbo.FactInternetSales f JOIN dbo.DimCustomer c ON c.CustomerKey = f.CustomerKey GROUP BY c.FirstName, c.LastName; -- Log the end time of the stored procedure UPDATE dbo.StoredProcedureLog SET EndTime = GETDATE() WHERE SPName = 'usp_GetSalesForCustomer' AND EndTime IS NULL; END CREATE PROCEDURE usp_GetProductSales AS BEGIN WAITFOR DELAY '00:00:04'; -- Log the start time of the stored procedure INSERT INTO dbo.StoredProcedureLog (SPName, StartTime) VALUES ('usp_GetProductSales', GETDATE()); SELECT p.EnglishProductName, SUM(f.SalesAmount) AS TotalSales FROM dbo.FactInternetSales f JOIN dbo.DimProduct p ON p.ProductKey = f.ProductKey GROUP BY p.EnglishProductName; -- Log the end time of the stored procedure UPDATE dbo.StoredProcedureLog SET EndTime = GETDATE() WHERE SPName = 'usp_GetProductSales' AND EndTime IS NULL; END CREATE PROCEDURE usp_GetTopSellingProducts AS BEGIN WAITFOR DELAY '00:00:06'; -- Log the start time of the stored procedure INSERT INTO dbo.StoredProcedureLog (SPName, StartTime) VALUES ('usp_GetTopSellingProducts', GETDATE()); SELECT TOP 10 p.EnglishProductName, SUM(f.SalesAmount) AS TotalSales FROM dbo.FactInternetSales f JOIN dbo.DimProduct p ON p.ProductKey = f.ProductKey GROUP BY p.EnglishProductName ORDER BY TotalSales DESC; -- Log the end time of the stored procedure UPDATE dbo.StoredProcedureLog SET EndTime = GETDATE() WHERE SPName = 'usp_GetTopSellingProducts' AND EndTime IS NULL; END --Call the send_parallel_proc_message procedure for each stored procedure you want to execute in parallel: EXEC dbo.send_parallel_proc_message 'usp_GetSalesForCustomer'; EXEC dbo.send_parallel_proc_message 'usp_GetProductSales'; EXEC dbo.send_parallel_proc_message 'usp_GetTopSellingProducts'; --Start the parallel_proc_receiver stored procedure to execute the stored procedures in parallel EXEC parallel_proc_receiver; --check start and endtime of procedures, starttime should be the same select * from StoredProcedureLog

I like to enable the Service Broker on all of my non-system databases. I'd probably put it in model, but I recall something preventing that. What reasons are there, no matter how far-fetched, for not enabling the Service Broker everywhere? All that I could find is that it's difficult to grab an exclusive lock in some databases.

After configuring two Service Broker instances that successfully send msg to remote server on separate databases, I am trying to set up a second service broker instance on one of the same, successful, databases. I am receiving errors on ssbdiagnose, only. No other tools show errors. 2 Errors from ssbdiagnose, when running on the source server. 1. 'target IP' 'targetDatabaseName' The certificate 'source cert name' used by user '' for dialog security is not deployed in the database. 2. 'target IP' 'targetDatabaseName' The user '' from sourceDatabase '' on 'source IP' cannot be mapped into this database using certificates. The source cert name from the first error msg is my previously existing certificate on the source server that is used to send msg to a different database on the target server. So it appears to be looking at the wrong certificate. When running ssbdiagnose on the target server, I get the following error: 1. Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication. Thank you

I have a mirrored database that was using a service broker to push notifications to a client. The broker creation framework started making duplicates and this caused serious problems so i rolled back to a different framework. The problem is i cannot cleanup the the existing services that are not longer in use (or should be). This line hangs indefinitely. DROP SERVICE [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] * I've tried stopping the broker but i cannot because it is a mirrored database. * I've tried killing every single transaction in the database to free the lock but that didn't work. * I've tried working my way up by disabling the queue and then deleting the contracts and message types but i cannot while the service still exists. * I was able to disable the queues and delete the activation stored procedures but this didn't seem to do anything. * Most of the queues do appear to have some rows in them. * SELECT * FROM sys.transmission_queue hangs indefinitely as well. * EDIT: i was able to empty the queue with the following, but it didn't actually fix the issue. DECLARE @dialog UNIQUEIDENTIFIER WHILE EXISTS (SELECT 1 from ) BEGIN WAITFOR( RECEIVE TOP (1) @dialog = conversation_handle FROM ), TIMEOUT 500 end conversation @dialog with cleanup end I'm afraid i'm going to have to break mirroring in order to make a new broker. This is a live production system and i'd rather not do that.. Anything else i can try? **BIG EDIT** So i was able to finally figure out a way to delete ***most*** of them. I first issued this to empty out the queue -- Create variables used to hold information DECLARE @dialog UNIQUEIDENTIFIER WHILE EXISTS (SELECT 1 from [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]) BEGIN WAITFOR( RECEIVE TOP (1) @dialog = conversation_handle FROM dbo. [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] ), TIMEOUT 500 end conversation @dialog with cleanup end Then i altered the queue with ACTIVATION ( DROP ) ALTER QUEUE [dbo]. [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( DROP ), POISON_MESSAGE_HANDLING (STATUS = OFF) After this i was able to drop the service and then the queue. It only seems to take 3-4 minutes per queue to delete. DROP service [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] DROP QUEUE [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] At this point all but 2 of the 15 services that refused to drop to have been deleted after stopping and retrying over and over again. **There obviously is something blocking here but as discussed in the comments there isn't anything blocking when i run SELECT * FROM sys.dm_exec_requests**

I'm using a instance with Microsoft SQL Server 2016 || Enterprise Edition and I need to enable Service Broker for my database with this sentence: ALTER DATABASE DB SET ENABLE_BROKER; The size of the database is approximately 3 TB and is on a AG Group, I saw two main options: add the command with rollback immediate or to stop the sql service agent. I think the best option for me is to stop the agent but I confused, It is necessary to stop the agent only in the primary node or I have to stop in primary & secondary nodes?

For some reason, we have multiple processes that get a request to update some tables (legacy code). Let's say we have 2 process that simultaneously updating MyTable1 and MyTable2 in a transaction. UPDATE MyTable1 SET ... WHERE Id=@p UPDATE MyTable2 SET ... WHERE Id=@p We have PK in Id column. Both processes are updating the above tables in same order as above. I am just trying to understand why we have deadlock in this case, as if one process update MyTable1 then row get locked and second process will be in waiting state, no? Here is deadlock log, unknown unknown (@p1 int,@p0 int)SET NOCOUNT ON; UPDATE [MyTable1] SET [MyColumn1] = @p0 WHERE [Id] = @p1; SELECT @@ROWCOUNT; unknown unknown (@p1 int,@p0 int)SET NOCOUNT ON; UPDATE [MyTable2] SET [MyColumn1] = @p0 WHERE [Id] = @p1; SELECT @@ROWCOUNT; unknown unknown (@p1 int,@p0 int)SET NOCOUNT ON; UPDATE [MyTable2] SET [MyColumn1] = @p0 WHERE [Id] = @p1; SELECT @@ROWCOUNT; unknown unknown (@p1 int,@p0 int)SET NOCOUNT ON; UPDATE [MyTable1] SET [MyColumn1] = @p0 WHERE [Id] = @p1; SELECT @@ROWCOUNT; My Code is simple as that, public class MyConsumer : IConsumer { private readonly MyService _myService; public AddUpdateHubLinkConsumer(MyService myService) { _myService= myService; } public async Task Consume(ConsumeContext context) { await _myService.SaveMyChanges(context.Message); } } public class MyService { public async Task SaveMyChanges(MyModel model) { // My work there await myDbContext.SaveMyChanges(); } }

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 版权声明：本文为博主原创文章，转载请附上博文链接！

I found some older posts dealing with this(5 yrs old or more), but I am confused on the process or if this information is out of date, hence my new question. I am on sql 2016 SP2-CU11 enterprise. I need to enable service broker on a database that is in an availability group. Its 1.8TB in size, so I really don't want to take it totally out of the AG and reseed it all over again with a fresh backup if I can avoid it. Some older posts say you have to completely remove and add the db back in the AG, others reference just running some code to create endpoints, and routes which I have never run across before. Is the process below the only way to enable service broker on a DB in an AG where it is not enabled? 1. remove the secondary copy of the DB, then the primary copy from the AG. 2. Run below code to activate service broker: ALTER DATABASE XXXXXX SET ENABLE_BROKER; GO 3. Take a full backup, restore it to the secondary with no recovery. 4. join the DB to the AG on the secondary.

I'm planning a project to upgrade internally-developed app from SQL Server 2005 to SQL Server 2012. I hope to use Service Broker as a bridge while in transition. Are there any issues to consider in a forward-compatibility scenario like this other than newer features like multicasting not being present? I can't find anything in BOL specifically about compatibility.

one of my clients has a database that works with service broker, last week we tried to move the database to an always on environment without knowing the application uses the service broker. after there were complaints that it didn't work(scheduled meetings were not added to the calendar and did not close when old ones were done (its a clinic application)) we found Microsoft reference to service broker with always on and tried to use it: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/service-broker-with-always-on-availability-groups-sql-server?view=sql-server-2017 but it still didnt work. at first we tried alter database [databasename] set enable_broker with rollback immediate alter database [databasename] set new_broker with rollback immediate and we received an error > Msg 1468, Level 16, State 1, Line 2 The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. Msg 5069, Level 16, State 1 we also tried these commands: CREATE ENDPOINT [SSBEndpoint] STATE = STARTED AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL ) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS) GRANT CONNECT ON ENDPOINT::[SSBEndpoint] TO [PUBLIC] ALTER ROUTE AutoCreatedLocal WITH ADDRESS = 'TCP://[server]:4022' ; at the end we didn't have a choice and we took it out of the always on group and after executing this command it worked: ALTER ROUTE AutoCreatedLocal WITH ADDRESS = 'LOCAL' ; does anyone have advise of what we are missing here on the configuration? thanks.

I have also posted this question on my blog: [http://www.sqldiablo.com/2012/04/15/service-broker-alwayson-availability-groups-odd-transmission-queue-behavior/](http://web.archive.org/web/20160907121946/http://www.sqldiablo.com/2012/04/15/service-broker-alwayson-availability-groups-odd-transmission-queue-behavior/) . I’ve been working on a project over the past several months that will utilize Service Broker and AlwaysOn Availability Groups to meet some of the HA and DR goals of the company I work for (more info: [http://www.sqldiablo.com/service-broker-replication/](http://web.archive.org/web/20140814064905/http://www.sqldiablo.com/service-broker-replication/)) . Just recently, I was able to implement the full solution in my development lab and point an instance of our website at it. While we were working out some kinks in our database and website to get the two working well with my Service Broker Replication project, I began noticing some odd behavior in Service Broker when it’s used with AlwaysOn Availability Groups, and I wanted to blog about it in an attempt to see if anyone else has seen this issue and might have an idea how to address it. # The Setup: # I have a Hyper-V host running 6 Windows Server 2008 R2 VMs (BTDevSQLVM1-BTDevSQLVM6). The VMs are grouped into 2-node WSFCs with node and file share quorum. I’ve installed standalone SQL 2012 Developer Edition instances on each of the VMs, and created an Availability Group with a listener on each cluster (SBReplDistrib, SBRepl1, & SBRepl2). For the purpose of this blog post, I’ll be focusing on the communication between SBRepl1 and SBReplDistrib. The illustration below shows the Service Broker objects for each side of the conversation: (I'm new and can't post images yet, so please see my blog at the URL above for the image) The Service Broker endpoints and routes are setup per [this MSDN article](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/service-broker-with-always-on-availability-groups-sql-server?redirectedfrom=MSDN&view=sql-server-ver16).The SBRepl_Receive route in MSDB is for the local server’s service (//SBReplDistrib/SBRepl on SBReplDistrib, and //SBRepl1/SBRepl on SBRepl1), and points to the local instance. The SBRepl_Send route on SBRepl1 maps service //SBReplDistrib/SBRepl to TCP://SBReplDistrib:4022, and the SBRepl_Send_SBRepl1 route on SBReplDistrib is a similar mapping for the service on SBRepl1. # The Expected Behavior: # My understanding of how Service Broker handles message sending and receiving is thus (This is pretty simplified. There is a lot more detail about this process in Klaus Aschenbrenner’s book “Pro SQL Server 2008 Service Broker”): 1. The initiator app creates a message (in this case, well formed XML) 2. If there is an existing dialog conversation between the initiator service and the target service that is in the conversing status, the app can simply send the message on the existing conversation handle. Otherwise, the initiator app should begin a dialog conversation between the initiator service and the target service and send the message on that conversation handle. 3. The message is placed in the sys.transmission_queue system table and Service Broker begins making attempts to deliver the message to the target service. 4. Service Broker looks for an appropriate route and remote service binding and uses them to determine the address to connect to in order to deliver the message. 5. Service Broker opens a connection to the target, authenticates, and delivers the message to the target service broker. 6. The target Service Broker attempts to classify the message and determine what local service will handle the message (it uses route data in the msdb database for this). 7. The target Service Broker delivers the message to the target service’s queue 8. Once the message is successfully delivered to the target queue, the target Service Broker looks for route information back to the initiator and attempts to deliver an acknowledgement that the message was received. 9. The initiator’s Service Broker receives the acknowledgement and uses routing information in MSDB to determine what local service the acknowledgement is for. 10. Upon successful routing of the acknowledgement to the initiating service, the message is then removed from the sys.transmission_queue system table. 11. If the initiator does not receive an acknowledgement that the message was received, it will periodically retry delivering the message to the target. If the target has already received the message, it will simply drop any additional delivery retries and send acknowledgements for them. # The Odd Behavior: # Step 11 is where I am seeing some very odd behavior with Service Broker and AlwaysOn. I see the message getting delivered to the target and processed successfully, and I also see the acknowledgement getting sent back to the initiator and received. However, the message remains in sys.transmission_queue as though no acknowledgement was received. To make things even more strange, Service Broker isn’t attempting to resend the message like I would expect it to if the acknowledgement wasn’t received. Instead, the message simply remain in the sys.transmission_queue, and as new messages are sent, they get delivered, acknowledged, and they too remain in the sys.transmission_queue. It seems to me like service broker is getting the acknowledgements and therefore stops trying to deliver the message, but doesn’t remove it from the sys.transmission_queue for some reason. The transmission_status for these messages remains blank, which should indicate that Service Broker hasn’t attempted to deliver them yet. I checked the retention setting on the service queue, and it is set to off, but that should only impact the service queue and not the sys.transmission_queue. I have also traced both sides of the conversation using SQL Profiler, and I am able to see the message getting sent and the acknowledgement being sent back to the initiator and getting received (see XML trace data at the end of this post). One odd thing did jump out at me in the traces though. I noticed that both sides seemed to be a bit confused about the TCP connections, because messages are sent from the IP address of the node itself while the service routes and the messages themselves point to the name/IP of the AG listener. This confusion appears to be causing each side to close the existing connection between the two services and create a new one in order to deliver a message or acknowledgement. I’m not sure if this is normal or not or if it has anything to do with why the acknowledgements aren’t being handled correctly, but it was the only thing I could see that could possibly explain the odd behavior. # The Plea for Help: # At this time, I don’t have a solution to this message retention issue other than to manually end the conversation with cleanup on both sides, and that’s not really something I want to do. If you have any ideas as to why this might be happening or what I can do about it, please leave me a comment and let me know. If there is any additional information that you would like me to provide about my setup or about the issue, please let me know in the comments as well. I will post a followup to this post if/when I find a solution to this issue. # The Trace Data: # Please see my blog post (the URL is at the beginning of the question).

I am aware of the difference between a service broker (centralized) and service bus (decentralized). Is it not that multiple SQL Servers communicating using their Service Broker are considered a decentralized service bus? Why not? Many thx.

The following query return 1 for is_broker_enabled for a couple of databases. However, I don't recollect ever using the service broker feature. SELECT db.database_id, db.[name] AS [Database Name], is_broker_enabled FROM sys.databases AS db How to check if the service broker is enabled at the server level and whether it is being utilized?