provides the infrastructure for queue-based asynchronous message communication between databases.
2
votes
1answer
34 views
Is the Service Broker good for publishing data between servers?
I'm trying to find a better solution when publishing data to a remote table.
So we have Server A and Server B. Server A holds a master table which should be replicated to Server B.
Currently, we ...
0
votes
0answers
40 views
How can I use Query notification and what are the requirement? [duplicate]
I want to use Query notification in my app to get the notification from database for updated data, so that app will fire the query to get updated data instead of monitoring the database for every 5 ...
2
votes
1answer
114 views
Service Broker stops working
I'm new to Service Broker. Our existing Service Broker suddenly stops working and I can't figure out why.
When I try to enable Service Broker (which is already enabled) I get this in SQL error log:
...
5
votes
1answer
91 views
SA permissions issues with many nested objects
I have a broker application that's relatively complicated.
Today, after I made some changes, I started getting the error:
The server principal 'sa' is not able to access the database 'XYZ' under ...
4
votes
1answer
62 views
Recommendations on how to organize Queues in Service Broker
I have a service broker application that currently has 5 or 6 queues on two servers.
The general workflow is:
Server A
User populates some tables
User fires stored proc which puts a message into ...
1
vote
0answers
174 views
DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'
I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
0
votes
0answers
32 views
How to bind contract to service?
I have a service broker setup where server A is updating server C with no problem. I now need to add another service broker setup where server B also updates the same database on server C.
When I ...
0
votes
0answers
23 views
Monitor For Disabled Queue On RDS SQL Server
I would like to somehow get notification of a queue being disabled due to poisoned messages. (The issue is occasional resource contention. Until we solve it, monitoring would help us be aware.) In my ...
0
votes
1answer
70 views
Generate script for SQL Broker objects
I'm using SQL Server Management Studio to generate scripts for all the objects in an existing database:
but the objects related to SQL Broker (messages, queues and services) are not generated.
...
1
vote
1answer
68 views
Service Broker - Communication not happening between servers despite ssbdiagnose saying all is well
I am setting up Service Broker communication between 2 servers, A and B we will call them. I used this article as an example:
...
1
vote
1answer
87 views
Effects of enabling service broker
I would like to use SqlCacheDependency to update a dataset in real-time, but we have not enabled the service broker on our production database. Could enabling the service broker have any adverse ...
0
votes
1answer
144 views
Query notifications and connection options
I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition.
.NET errors show a problem reported from SqlNotificationInfo ...
0
votes
0answers
51 views
Service Broker application won't send message between queues [closed]
I made this Service Broker application: http://pastebin.com/M1jZg2Pt
I want to run stored procedure spInsertLogLine and for a record to end up in table log_line.
I do
exec ...
2
votes
1answer
265 views
Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed
I've got this sequence of error messages showing up regularly in my server error log:
Error: 9245, Severity: 16, State: 1.
During the last time interval 257 query notification errors were ...
3
votes
0answers
146 views
SqlDependency permissions
The db is SQL Server 2012. All data tables are in the dbo schema. All data access is done via stored procedures in a different schema (user rt). I want to add query notifications for some tables, so I ...
4
votes
1answer
110 views
Large Transaction Log Backups in Service Broker-enabled system
We have a system that uses Service Broker for asynchronous messaging, with a fairly high throughput of messages. We don't really care about the persistent features of SSB in this scenario -- the ...
4
votes
1answer
142 views
Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly?
I kick off the following command in my app:
WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)
When I run `sp_who2 active', I get Status=SUSPENDED and Command=DELETE
Note: ...
12
votes
2answers
173 views
Does firing off an indefinite WAITFOR increase the log file size?
In the last release of my app, I added a command that tells it to wait when something arrives in the Service Broker queue:
WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)
The ...
9
votes
1answer
803 views
SQL Server Cache Flush and Disk I/O
We're busy load testing an OLTP system we've developed in .NET 4.0 and runs SQL Server 2008 R2 in the back. The system uses SQL Server Service Broker queues, which are very performant, but we are ...
1
vote
2answers
140 views
sql server removing service_queues
Hi when i query select * from sys.service_queues in sql server 2008 sp3
i am getting
InternalMailQueue
ExternalMailQueue
_LakeSide_DbTools_DeadlockQueue
QueryNotificationErrorsQueue
...
0
votes
1answer
53 views
UTC date in DMV dm_broker_queue_monitors
It seems that the DMV dm_broker_queue_monitors returns UTC time rather than local time. Why is it so?
I am wondering how to get local time from this view? I can get current time offset, but that is ...
0
votes
1answer
57 views
How many connections reads queue
Is there a way to get to know how many external tasks (console applications) is reading queue?
I am using Service Broker external activator.
What i want is something similar to information i get ...
5
votes
2answers
290 views
Performance impact of waiting on a service broker message
I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs). These messages are picked up by yet another client app and then processed. The way the ...
3
votes
1answer
580 views
Enabling Service Broker on a database involved in an availability group
I'm trying to enable Service Broker on a SQL Server 2012 database that is part of an availability group and keep getting this error message:
The operation cannot be performed on database "dbname" ...
3
votes
1answer
269 views
Using Service Broker internal to a single SQL Express instance?
I understand that SQL Express cannot send Service Broker messages to other instances without a Standard (or better) instance involved in the chain. But is it possible to use Service Broker to send ...
3
votes
0answers
292 views
Event Notification / Service Broker - no inserts anymore, “previously existing connection with the same peer”
I've been working on getting several servers to feed Event Notifications to a central server. Had everything working yesterday, then managed to break it. Dropping and recreating the ...
4
votes
1answer
261 views
SQL Server 2008 R2 Service Broker Troubleshooting
I set up Service Broker between two databases on separate instances, one on a 2008 R2 Cluster the other on a separate box also 2008 R2. I have gone through the process of setting up all the objects ...
6
votes
0answers
363 views
Service Broker: Queue Monitor is dropped after poison message
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 ...
3
votes
1answer
192 views
Check Index Fragmentation on SQL Server Audit Queues
I've read this excellent article, http://rusanu.com/2010/03/09/dealing-with-large-queues/, and seem to be having the same issue, due to a low space issue, the service broker was turned off on a ...
1
vote
2answers
493 views
How to make a flexible service broker receive in a stored sql procedure, how to pass “FROM” parameter into receive
a while ago I asked a question about how to make begin dialog and send more flexible so that it can be embedded in a procedure that takes the to, from, parameters as sysname variables.
Service Broker ...
2
votes
1answer
181 views
Any problem with same conversation handle used by multiple services communicating with one target queue in a multiplexed service broker order queue?
I asked a question about implementing conversation groups in a SQL Service Broker setup which has several User Services communicating with the Server Service (Processing xml data requests to be sent ...
3
votes
1answer
196 views
Service Broker Procedure that will be used by multiple initiators to begin dialog conversation.. how to pass a parameter into it
The standard format for beginning a dialog is like this:
Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE initiator_1
TO SERVICE 'target_1'
ON ...
6
votes
3answers
230 views
Where to learn SQL Server Service Broker?
Are there any good resources to learn Service Broker?
I'm looking for a newbie-friendly guide.
1
vote
1answer
183 views
Can we use SQL Express to send messages to SQL Standard using the Service Broker?
We want to use SQL Express at our local sites but to still be able to push data back to our head office using the Service Broker. SQL Server at the head office is 2008 R2 Standard.
Looking at the ...
4
votes
1answer
441 views
Service Broker & AlwaysOn Availability Groups: Odd Transmission Queue Behavior
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/.
I’ve been working on a project over the ...
3
votes
1answer
196 views
Activity Monitor showing long duration for WAITFOR RECEIVE
In Activity Monitor, the following query is causing high CPU and 281ms average duration.
WAITFOR (
RECEIVE conversation_handle
, service_contract_name
, message_type_name
, message_body
...
1
vote
1answer
101 views
Recommendations on creating user-objects on system database
I want to setup a bunch of Event Notifications on SQL Server using Service Broker for our production database.
I've setup a new database on my testing machine, and everything works well.
The problem ...
1
vote
1answer
370 views
Elegant way of sending e-mails with Service Broker
Is there an elegant way for sending e-mails using a Service Broker service/queue?
I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to send e-mails to operators for each message on ...
1
vote
1answer
301 views
Setting Alerts for Autogrow
Can I set alerts for an autogrow event in SQL Server (2005 or 2008 R2)?
I've searched around and haven't found the 'error number' for autogrowth, if there is one.
In case this isn't possible, how ...
4
votes
1answer
443 views
How do query notifications detect changes in the query result?
I am working with a legacy database that has implemented query notifications against a table called ColorList.
While running a server-side trace for an unrelated issue, I noticed the query
SELECT ...
2
votes
1answer
367 views
Status for a service broker queue
Looking at the sys.service_queues DMV, I see that there are columns called "is_enqueue_enabled" and "is_receive_enabled". However, in the "ALTER QUEUE" DDL, there only seems to be a "STATUS" that can ...
0
votes
2answers
98 views
Service Broker to Send Messages Specific Database bases based on critiera
In Message Broker can you configure to use one queue but based on criteria send to a different Databases or is it a one to one relationship? I am tryibg to figure out if I should use Message broker or ...
2
votes
1answer
182 views
Accessing Service Broker from Different Databases
In my Application we have a lot of databases nearly 100 dbs.
We are planning to use Service Broker for calling a Stored Proc Async.
Instead of running Service Broker on all the Databases, Is it ...
1
vote
4answers
612 views
The broker in the sender's database is in single user mode
Could you please help with why am I getting this sys.transmission_queue.transmission_status message after restoring from a snapshot?
The broker in the sender's database is in single user mode. ...
1
vote
0answers
295 views
Can the service-broker cause SQL Server to crash when using SQLDependency
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 ...
1
vote
1answer
376 views
SQL Server Service Broker and Deadlocks
I am reading through SQL server books online for the first team whilst learning how to use the service broker and I came across this statement:
The application that implements the service reads ...
