Skip to main content
The 2024 Developer Survey results are live! See the results

Questions tagged [service-broker]

provides the infrastructure for queue-based asynchronous message communication between databases.

Filter by
Sorted by
Tagged with
0 votes
0 answers
47 views

High CPU utilization on an idle SQL Server instance without user traffic

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 ...
Martin Karouš's user avatar
-4 votes
1 answer
84 views

What reasons are there to not enable the service broker? [closed]

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 ...
J. Mini's user avatar
  • 555
0 votes
0 answers
41 views

Delayed 'Update' Message Insertion in MSSQL Broker: Troubleshooting and Resolution

I employ MSSQL 2019 triggers for 'insert,' 'update,' and 'delete' operations and utilizing MSSQL Broker to generate messages for consumption as audit messages. Each message triggers a specific row ...
Seyed Jafar Mousawi's user avatar
0 votes
1 answer
138 views

SQL Server Service Broker on a High Availability Group

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 ...
Carolina's user avatar
0 votes
0 answers
83 views

Updating 2 tables in same order by 2 different process yield deadlock

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. ...
Imran Qadir Baksh - Baloch's user avatar
2 votes
1 answer
907 views

SQL Server: Using Service Broker to parallely run several stored procedures

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 ...
Jessy's user avatar
  • 21
0 votes
1 answer
260 views

Why is SQL Server Service Broker a broker and not a bus?

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 ...
geeko's user avatar
  • 31
0 votes
0 answers
2k views

An error occurred while receiving data: '24(The program issued a command but the command length is incorrect.)'

When I try to setup ServiceBroker communication between 2 SQL Server instances, based on different Azure IaaS servers, sys.transmission_queue.transmission_status mentions the following error I am not ...
tivivi's user avatar
  • 41
2 votes
0 answers
111 views

Service Broker enabled on tempdb in SQL Server

I am seeking an answer what might me the reason/scenario to have Service Broker enabled on tempdb. I have a legacy SQL Server and it is enabled there. Is it safe to disable it? Is it possible to ...
Bartosz Siemasz's user avatar
0 votes
0 answers
427 views

Exception during GRANT CONNECT ON ENDPOINT to group managed service account

Did somebody happen to make a GRANT CONNECT ON ENDPOINT:: operation to a group managed service account work? Personally, I can't. I am always facing the so-called self-explaining exception: Msg 15151, ...
tivivi's user avatar
  • 41
2 votes
0 answers
365 views

Service Broker performance issues after upgrading from 2012 to 2017 - tons of LCK_M_IX

Last night we upgraded a SQL Server 2012 server to SQL Server 2017 + CU 29. This is one of a matched set - we send the same message to both and each processes it. No, this is not AlwaysOn; we're ...
mbourgon's user avatar
  • 433
-1 votes
1 answer
2k views

How to check if the service broker is enabled at the server level and whether it is being utilized?

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], ...
variable's user avatar
  • 3,476
4 votes
1 answer
292 views

What are the advantages of a long timeout for RECEIVE in activation procedures?

The RECEIVE statement has an optional timeout parameter. Most of the activation procedure examples online use a value of 1000 or 5000 milliseconds. In my own testing with Service Broker, I've found ...
Joe Obbish's user avatar
  • 32.5k
0 votes
1 answer
393 views

Service Broker limit in SQL Server

As mentioned in the Link below, For client-side applications, query notification users should not exceed ten concurrent users , in SQL Server 2005. What is the maximum number of concurrent users ...
IT Researcher143's user avatar
1 vote
0 answers
129 views

Service Broker's SEND statement causes security context switch

We have triggers in multiple tables that will send messages to a Service Broker service upon INSERT, UPDATE and DELETE operations. These operations may be nested as parts of bigger, transactional ...
Crono's user avatar
  • 111
1 vote
2 answers
830 views

Correct way to continuously receive from a queue

I have to receive messages from a queue continuously. My current code looks like this: DECLARE @status INT=1 WHILE @status IS NOT NULL BEGIN WAITFOR (RECEIVE @status=status, ...
Felice Pollano's user avatar
0 votes
1 answer
27 views

Creating specific permisions for DB user

I need to give access to CREATE QUEUE and DROP QUEUE queries. Can`t find that information over the internet, only found that db_ddladmin or db_owner fixed database roles, or the sysadmin have that ...
Valentinas's user avatar
2 votes
0 answers
97 views

SQL Server RECEIVE statement ordering issues

I'm seeing some unexpected message ordering behavior running a RECEIVE statement against a Service Broker queue. According to the documentation: For a given conversation, a RECEIVE statement returns ...
kirchner's user avatar
  • 318
1 vote
1 answer
197 views

sqlpackage.exe fails on SignalR objects

Issue: When running sqlpackage.exe to apply a sql dacpac, it occasionally fails on broker/SignalR related objects, which breaks the build: *** Could not deploy package. Error SQL72014: .Net SqlClient ...
Greg's user avatar
  • 574
0 votes
0 answers
732 views

Service Broker error when ending a conversation. Second attempt

Let me rephrase my previous question because issue reproduction is much simpler than I thought. When I close Service Broker dialogs sometimes I get a message The remote conversation endpoint is either ...
Pavel Zv's user avatar
  • 183
0 votes
0 answers
575 views

Service Broker error when ending a conversation

I use Service Broker infrastructure to develop data push approach between multiple servers ( many sources and one destination ). I stick Remus Rusanu recommendations about how reuse or end ...
Pavel Zv's user avatar
  • 183
0 votes
1 answer
2k views

activate Service Broker on a database in an Availability Group

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 ...
DBA Greg14's user avatar
3 votes
1 answer
2k views

Why the sudden increase of my database .mdf file size?

Suddenly my database .mdf file size is increased to more than 250GB but my total table data size count is 4GB only. I did a data shrink, but there are no results. Then I did the query below: ...
Makdumul Islam's user avatar
1 vote
1 answer
8k views

How to run Sql Server stored procedure in Async mode?

Is there a way to run SQL Server 2019 stored procedures with parameters asynchronously? Calls to this SP will be made several times a day, i.e. this is not one time requirement. Service Broker can be ...
snaseer's user avatar
  • 11
0 votes
0 answers
736 views

Event log filling up with Sql error

Windows event log filling up with ERROR 1: "The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-]' running on queue 'test.dbo.SqlQueryNotificationService-' output the following: '...
IT researcher's user avatar
1 vote
0 answers
220 views

service broker queue index growing beyond control

We have a queue that was set up about ten years ago in 2008. We have been running it on an Azure VM Standard edition sql 2016 for about 3 years no with no problems. In the last 3 months, with no ...
Pushpendra Rishi's user avatar
0 votes
1 answer
461 views

Service Broker SEND performance

Previously I asked about latch contention / locks which can happen during Service Broker work: Service Broker locks. I got a great answer on general question but I still have narrowly focused question....
Pavel Zv's user avatar
  • 183
3 votes
1 answer
2k views

Service Broker locks

The situation: I use Service Broker to develop data push approach. Now I consider a scenario: Use separate DB ( called Broker ) which has services, queues and stored procedure to send data. Use ...
Pavel Zv's user avatar
  • 183
0 votes
0 answers
416 views

Sql service broker and SqlDependency

I have a system where my TCP/IP command prompt application receive data and insert that data in [database1].[mytable]. Now I want to move that data from [database1].[mytable] to [database2].[...
deepak's user avatar
  • 101
0 votes
1 answer
448 views

The service broker is administratively disabled

I have just restored some databases what use service broker from LIVE to staging. it is not working and when I put a profile trace to run it only shows messages related to database id 1 and 4, ...
Marcello Miorelli's user avatar
1 vote
1 answer
2k views

Service Broker Error - The conversation handle is not found

A new third party application using Service Broker on SQL Server 2017 is causing the error log to report numerous failed query delivery notifications due to conversation handle not found. I've ensured ...
sqldband's user avatar
2 votes
2 answers
901 views

Adding a database with Service Broker to an existing Availability Group on a new replica

Server 1 - SQL Server 2016 (Primary in existing AG) Server 2 - SQL Server 2016 (Secondary in existing AG) Server 3 - SQL Server 2016 (Offsite Secondary in existing AG) Server 4 - SQL Server 2017 (New ...
Luke's user avatar
  • 23
1 vote
1 answer
5k views

DNS lookup failed with error: '11001(No such host is known.)'

I have a server in the USA that should connect to one of our servers in Europe through service broker. The server in Europe is an Availability group, whose listener is called sqlst. when I run the ...
Marcello Miorelli's user avatar
6 votes
1 answer
910 views

Find which databases have a particular service in SQL Server Service Broker

From what I currently understand, the names of services are global to the instance and you can refer to them without knowing which database contains the service and associated queue etc. In addition, ...
Cade Roux's user avatar
  • 6,602
1 vote
1 answer
130 views

What is the permission necessary to drop a service in service-broker?

When I want to drop a service which is already created, this error happens: Msg 15151, Level 16, State 1, Line 316 Cannot drop the service '//Audit/Contract', because it does not exist or you do not ...
mostafa8026's user avatar
8 votes
0 answers
1k views

SQL Server 2012+ & Service Broker: SSBDT Spam ending conversations after sending message to NULL conversation_handle

I'm mostly posting this for community awareness, but feel free to give feedback if you think there is a solution other than coding around the problem as we are doing. Crossposting this from my bug ...
G.Smith's user avatar
  • 123
1 vote
0 answers
2k views

Service Broker - A previously existing connection with the same peer was detected during connection handshake.

There are good questions about service broker here, here and here. In none of those questions I see the following error message: Service Broker login attempt by user 'MYCOMPANY_USA\sqlservice.' ...
Marcello Miorelli's user avatar
0 votes
1 answer
152 views

time difference between two machines makes problem in service broker communication

I have two machines and each machine has a SQL Server instance. Between these machines I run service broker. One of my machines has a different time from the other. I mean the machine time of A is 9:...
Ehsan Akbar's user avatar
1 vote
0 answers
2k views

service broker - poison message - how to handle? how to enable poison messase handling?

I am getting constantly this error message on service broker: POISON MSSAGE - Message Removed from the queue to allow other messages to proceed - ApplicationID: 377411 select top 1000 * from ...
Marcello Miorelli's user avatar
0 votes
0 answers
341 views

is there a way to find out what route and endpoint my service broker session is using?

I have been trying in many way, from sys.conversation_endpoints to find out which route and which endpoint my service broker session is using. SELECT conversation_handle, is_initiator, s.name as '...
Marcello Miorelli's user avatar
2 votes
1 answer
479 views

is there any advantage when troubleshooting service broker, to create the queue with retention=ON?

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 ...
Marcello Miorelli's user avatar
0 votes
2 answers
2k views

How to script all the service broker elements for each database it is on, in the current server?

I have service broker running on different servers. Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there. For the next time I need to ...
Marcello Miorelli's user avatar
4 votes
1 answer
1k views

Stored Procedure triggered by Service Broker can't access another database in SQLServer 2017

I make an extensive use of SQL Server Service Broker to trigger/parallelize data science tasks (data imports with SSIS or direct queries to another database on the same server, then data management ...
Vincent Chalmel's user avatar
2 votes
1 answer
5k views

How do I completely reset ServiceBroker including all messages, queues, triggers, etc?

I have a database where I have a ServiceBroker up and running. For unknown reasons we have experienced a lot of problems with missing conversation handlers; I.e. The conversation handle "D33EC01C-...
Marcus's user avatar
  • 225
1 vote
0 answers
515 views

Service Broker process active for too long in SQL Sever

Let me acknowledge, I don't know much about database efficiency neither Service Broker as a whole, where I work the dba that used tp handle these kind of things left some weeks ago. The company uses ...
A. J.'s user avatar
  • 23
1 vote
1 answer
660 views

service broker with mirroring

After we tried configure a database with service broker into an always on environment and failed (the service broker is not working properly) we are now considering to try mirroring for high ...
sql_girl's user avatar
  • 345
-4 votes
1 answer
1k views

What type of messages does SQL Service Broker send?

SQL Server has Service Broker, which provides messaging and queueing functions between instances. I am new to using this technology and want to know which kind of messages does it send between ...
igelr's user avatar
  • 2,142
2 votes
2 answers
6k views

problem with service broker in always-on

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 ...
sql_girl's user avatar
  • 345
2 votes
1 answer
45 views

Does a Target Service need to do anything for a conversation that is ended?

We have two queues, SSBInitiator and SSBTarget. In my example situation, I have ran the following code to create a conversation: BEGIN DIALOG CONVERSATION @ConversationHandle FROM ...
DForck42's user avatar
  • 3,048
1 vote
0 answers
29 views

Central db with satelites

I am facing the following challenge. I want to have a central MySql server having all the data and several satellites servers that hold part of the data. Satellites servers will hold some original ...
Nianios's user avatar
  • 113