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

learn more… | top users | synonyms

13
votes
2answers
468 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 ...
11
votes
3answers
1k 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.
11
votes
1answer
952 views

Sch-M WAIT blocks Sch-S in SQL Server 2014 but not SQL Server 2008 R2?

We recently migrated our production instances from SQL 2008 R2 to brand new SQL 2014 servers. Here is an interesting scenario that we uncovered with our usage of Service Broker. Consider a database ...
11
votes
1answer
5k 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 ...
11
votes
0answers
106 views

Service broker was backed up, now receiving, but doesn't seem to be processing

Having a problem with Event Notifications. On the machine/drive/database that the messages are sent to (receiver), the drive filled up when nobody was looking, so it's been backed up all day. Now ...
7
votes
1answer
1k 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 ...
6
votes
1answer
2k 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 ...
6
votes
1answer
838 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 ...
6
votes
1answer
231 views

No service broker active on database

I am trying to set up query notification however when I try to create a queue and service I get the error 'There is no Service Broker active in the database. Change to a database context that contains ...
6
votes
1answer
1k 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 ...
5
votes
2answers
2k 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 ...
5
votes
1answer
874 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 ...
5
votes
1answer
382 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
606 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: ...
4
votes
1answer
466 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
2k 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 ...
4
votes
1answer
382 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 ...
4
votes
1answer
155 views

SQL Server Service Broker Compatability across versions?

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 ...
4
votes
1answer
38 views

sa doesn't have permissions to other database through synonyms with the Service Broker

I've been trying to get this Service Broker and timers thing to work for over a week straight now, so bear with me if this comes off as.. uh.. ragey. I'm also fairly inexperienced when it comes to SQL ...
4
votes
1answer
4k 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" ...
4
votes
1answer
2k views

SQL Server CPU spikes - possibly non-query related - suggested indicators

I'm looking for suggestions for indicators I could monitor to discover what is causing these cpu "blunted spikes". Some known information: sqlserver.exe is confirmed as the process consuming the ...
4
votes
1answer
59 views

Should Service Broker use SQL or Windows Authentication?

I was curious what the best practice would be, for properly setting up Service Broker in SQL Server 2008. Should I use a SQL authenticated user, or should I use a Windows NT Authenticated user based ...
4
votes
1answer
80 views

Query Notification setup

I am relatively new at playing around with this and have been trying to follow a tutorial on setting up in the section named "Prerequisites to Create SQL Notification Requests" within this site ...
4
votes
0answers
77 views

Take action from extended event?

I've seen it mentioned a few times that you can configure an event session to take an action when an event fires. Does anyone have any information on how to do this? I know that you can use the ...
4
votes
0answers
192 views

ENABLE_BROKER vs NORECOVERY

I have two nodes with MSSQL2012 which work with AlwaysOn High Availability After some crash I had to remove db from availability group on the one of the nodes. For restoration I've made backup ...
3
votes
2answers
3k 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 ...
3
votes
1answer
759 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 ...
3
votes
1answer
447 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 ...
3
votes
1answer
1k 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
1answer
1k 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 ...
3
votes
1answer
1k views

sql server service broker queue pass parameter to stored procedure

Stored Procedure Create PROCEDURE [dbo].[InitServiceLog] ( @name varchar(max)=null ) AS BEGIN SET NOCOUNT ON; insert into [AdventureWorks].[dbo].[ServiceBroker_Log](Service) ...
3
votes
1answer
463 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 ...
3
votes
1answer
20 views

SQL Server Service Broker - CHECKDB found consistency errors, how could this happen?

I encountered a weird SQL Server Service Broker consistency problem, namely I have a queue that holds messages (status=1, Ready to receive) which are lying there for a very long time. In the ...
3
votes
0answers
565 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 ...
3
votes
0answers
846 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 ...
2
votes
1answer
247 views

How to suppress activation procedure output from the error log?

I found so many below informational messages (bulk messages with no any error) are logged in the SQL Server error log. The activated proc 'x' running on queue 'y' output the following: 'z' I ...
2
votes
2answers
3k views

Why does the service broker require a master key after restoring to a different server?

I've been moving some databases which use the service broker to new servers recently via backup and restore. I've noticed that once I do this, these databases start throwing the database master key ...
2
votes
1answer
459 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 ...
2
votes
4answers
1k 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. ...
2
votes
2answers
85 views

Trigger to create a variable Table to be sent to service broker

I've setup a service broker for the first time and tested it... Looks good. Everytime a DML operation happens on a specific table; Let's say the Price Table, a trigger calls a stored procedure passing ...
2
votes
1answer
3k views

Permissions using .NET SqlDependency

SQLDependency Notifications work fine logged in as Windows Domain User, using Trusted Connection. But not as SQL-User: I created a db login, user and role as follows: CREATE LOGIN myLogin WITH ...
2
votes
1answer
1k views

Service Broker stops working [closed]

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: ...
2
votes
1answer
787 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 ...
2
votes
1answer
936 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: ...
2
votes
1answer
2k 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 ...
2
votes
1answer
349 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 ...
2
votes
1answer
58 views

Sql Service broker BEGIN DIALOG CONVERSATION and SEND. Should they be in a transaction?

I regularly see code that looks like this: BEGIN TRANSACTION; BEGIN DIALOG CONVERSATION @u.... SEND ... COMMIT; BOL for BEGIN DIALOG doesn't use the transaction handling in the examples and it ...
2
votes
0answers
150 views

Impact of Service Broker on Server Performance [closed]

For DDL auditing we are thinking to use service broker, but I want to check few parameters before deploying it to a production server, where there are about 200 DBs and Standard Edition of SQL Server. ...
2
votes
0answers
92 views

Service broker routes and DNS

Apologies for the long post but I've recently seen some strange behaviour with Service Broker when the routes it uses reference a DNS entry, and I am wondering if anyone has seen the same? We have ...
1
vote
1answer
198 views

sql server service broker

For the last few days the service broker command which run from master database is consuming much more RESOURCES than before. The Impact on the Database is high (from the bad side), It causes for ...