12
votes
Can't use msdb.dbo.sp_send_dbmail when in service broker - executes as guest?
I need to avoid marking the database as Trustworthy.
That is certainly the right attitude to have towards TRUSTWORTHY, and yes, this is possible.
So why is it being executed as guest in msdb when ...
- 69.5k
12
votes
Accepted
Why the sudden increase of my database .mdf file size?
Messages will be retained in Service Broker queues until they are consumed with RECEIVE or the associated conversations ended with END CONVERSATION. Messages will also be retained in the queue after ...
- 28.5k
10
votes
SQL Server Service Broker vs. RabbitMQ
Can we store the queue data in a durable manner and then fetch tasks
from specific queues based on business needs? (I assume yes, but just
confirming.)
Yes.
How reliable is it for 100+ queues? ...
- 47.6k
8
votes
SQL Server Service Broker vs. RabbitMQ
I thought I would add my 2 cents since I have been working quite a lot with both SSB as well as RabbitMQ (RMQ).
First of all: I agree with most of what David says in his post.The only thing I might ...
- 181
8
votes
Accepted
Set TRUSTWORTHY On smacks down service broker from working
Within each database there exists a dbo User. This User (Database-level) always exists, but the SID (Security IDentifier) that it maps to is not always the same; it will map to whatever Login (...
- 69.5k
7
votes
Accepted
Service Broker locks
IS it ok to use one queue and ~800000 triggers? :) I mean maybe there are some thresholds I need to consider.
No. Not really. You must ensure that your triggers are always short-running or your ...
- 47.6k
5
votes
Set TRUSTWORTHY On smacks down service broker from working
This can happen when you restore a database that was created on another server, and the database owner login account doesn't exist or has a different SID on the server you restored to.
Choose a login ...
- 913
5
votes
SQL Server: Using Service Broker to parallely run several stored procedures
Per CREATE QUEUE on the Learn site;
MAX_QUEUE_READERS =max_readers
Specifies the maximum number of instances of the activation stored procedure that the queue starts at the same time. The value of ...
- 70.5k
4
votes
Accepted
Service Broker activation procedure in C# with external dependencies
I suggest external activation rather than SQLCLR. That will provide capabilities like log4net that aren't easily doable in SQLCLR.
The trigger would begin a conversation and sends the project closure ...
- 28.5k
4
votes
The Service Broker endpoint is in disabled or stopped state - AoAG - SQL Server 2012 - 15 minute interval
According to this link,
It’s a default behavior. The message will show when AlwaysOn
availability group Listener is created and SQL Server binds the
Listener’s IP dynamically. There is no ...
- 2,193
4
votes
Accepted
problem with service broker in always-on
In order to have Service Broker work in combination with Availability Groups, you have to enable Service Broker for the database before adding it to an Availability Group.
Your question suggests that ...
4
votes
Accepted
Service Broker - Conversation Lifetime?
I know it's bad form to answer your own question, but I wanted to close this out for anyone who was interested. We finally did manage to resolve the issue, or at least resolve it enough to meet our ...
- 9,244
3
votes
Is there a service broker target for an extended event session
There looks to be a service_broker target in SQL Server 2017 but I can't find any documentation on it. This target is visible with the latest SSMS GUI and this query:
SELECT *
FROM sys....
- 28.5k
3
votes
Accepted
Drop service hangs indefinitely
Unfortunately i had to go the route i didn't want to. I had to break mirroring for the database and then create a new broker using:
ALTER DATABASE <db> SET NEW_BROKER WITH ROLLBACK IMMEDIATE
...
- 383
3
votes
Accepted
Service Broker-Certificate Not Found
Set up a profiler trace and monitor for Security Audit/Audit Broker Conversation. Repro the issue, an event of this class should be produced. Look at the event details. It should shed light. I think ...
- 52k
3
votes
Accepted
Does a Target Service need to do anything for a conversation that is ended?
From Microsoft Docs:
Both participants in a conversation must call END CONVERSATION for the conversation to complete.
If only one side ends the conversation, then the conversation metadata stays ...
- 70.5k
3
votes
How to run Sql Server stored procedure in Async mode?
The simplest way is to call sp_start_job and kick off an unscheduled SQL Agent Job. Parameter values can be stored in a table that the job reads from before calling the procedure.
- 47.6k
3
votes
Correct way to continuously receive from a queue
An agent job is a good "SQL Server" way to start an infinite job. as for your code, you could simply use
While 1=1
An agent job will not start another session while the existing one is ...
- 764
2
votes
Launching a python script from an insert trigger
To minimize the impact of running the Python script synchronously from your trigger, you can wrap your Python code into a BaseHTTPServer:
import BaseHTTPServer
class MyHTTPHandler(BaseHTTPServer....
- 26.9k
2
votes
Launching a python script from an insert trigger
"insert/update/delete happens in a table and a python script is triggered within 2 seconds of the db event,
First off, if you use a trigger to write a message into a table dedicated for this purpose,...
- 47.6k
2
votes
ENABLE_BROKER vs NORECOVERY
Yes, using ENABLE_BROKER in the RESTORE conflicts with NORECOVERY. If your database is in NORECOVERY you'll need to wait until it is recovered before you renable the broker. If you are having trouble, ...
- 535
2
votes
Service broker was backed up, now receiving, but doesn't seem to be processing
For the tcp endpoints, both sides, consider service acct used and connect permission --try re-granting, then new start on endpoints --> even if gui or dmv says started.
2
votes
Accepted
"Stored Procedures Invoked/sec" counter always increasing
There are several different types of performance counters, five of which are used by SQL Server (at least in terms of what you will find in the sys.dm_os_performance_counters DMV). You can see this ...
- 69.5k
2
votes
Accepted
Service broker not getting messages when enabled
To answer your questions :
Check default trace to see when it got disabled
when you altered the db with NEW_BROKER, it did more damage than you have thought of
If you created any route with ...
- 62.4k
2
votes
Accepted
Adding a database with Service Broker to an existing Availability Group on a new replica
Both SET TRUSTWORTHY ON and ENABLE_BROKER are database settings. When you backup and restore a database, these settings will be included in the bak-file and can be restored on the new SQL instance.
...
- 738
2
votes
Service Broker Error - The conversation handle is not found
You might want to try Microsoft's built-in ssbdiagnose utility.
The ssbdiagnose utility reports issues in Service Broker conversations or the configuration of Service Broker services. Configuration ...
- 18.3k
2
votes
Accepted
The service broker is administratively disabled
I got it fixed after reviewing and resetting all broker related database IDs.
First thing I did was to see what we have (on both source and target servers):
--========================================...
- 16.6k
2
votes
Accepted
sqlpackage.exe fails on SignalR objects
Again, the failure rate is like 1 out of every 10 runs, and no one is
manually removing these objects
Although no one is manually dropping these objects, it seems SqlDependency is used to detect ...
- 28.5k
2
votes
Accepted
Correct way to continuously receive from a queue
While the answer by Trubs does answer your questions and it might work great for you, I'd say the "correct" way to continuously receive from a queue is by using Service Broker Activation, ...
- 318
Only top scored, non community-wiki answers of a minimum length are eligible