Tag Info

Hot answers tagged

14

Some online articles: An Introduction to SQL Server Service Broker Service Broker Tutorials I myself host a few articles: Reusing Conversations Writing Service Broker Procedures Troubleshooting Conversations And there are several introduction level practical examples: Centralized Asynchronous Auditing with Service Broker Service Broker Foundations ...


13

Any active open transaction will pin the log, preventing truncation and eventually causing growth. If you start a transaction, write to the log and then wait forever in hope a message will eventually awake you, you just pinned the log and cause it to grow. Lately I started recommending people to shun the WAITFOR in activated procedure, along with the loop. ...


12

The behaviour did change between SQL Server 2008 R2 and SQL Server 2012. The 2008 R2 implementation was inconsistent with the documented 'relaxed FIFO' semantics: Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce ...


9

Others have already pointed out the culprit: SQL Server accumulates updates in memory (in the buffer pool) and only flushes them out periodically (at checkpoints). The two options suggested (-k and checkpoint interval) are complementary: -k will make the cause the checkpoint to produce less aggressive IO requests and last longer lowering recovery interval ...


7

Because is a DELETE. RECEIVe is just syntactic sugar for something like DELETE from queue OUTPUT DELETED.* WHERE status = <receivable>; If you enable retention then is an UPDATE, and will show as an UPDATE. There are some details, like how conversation group locking is done, how the proper message ExactlyOnceInOrder semantics are ensured and the ...


6

You could use a different database for your SSB load. It seems like your SSB data is transitory in nature. That would allow you to either switch to SIMPLE recovery model for that DB or throw away the log backups in case you need FULL for some other reason like mirroring. I don't think you'll be able to reduce log usage for SSB operations. I'm interested to ...


5

This is a red herring. WAITFOR (RECEIVE...) is by definition supposed to ... wait for messages! Therefore 281 seconds elapsed simply means that for 281 seconds there was no message to receive. In this case the application is the Database Mail external sending process that is sitting idle waiting for messages to be enqueued (ie. waits for sp_send_db_mail to ...


5

You are blocking a worker in SQL Server and workers are limited, subject to max worker threads. This means there should not be thousands of requests blocked in WAITFOR(RECEIVE...) or you'll starve the server of workers. But the first question that comes to mind is Why no leverage Service Broker Activation? This way you wouldn't be waiting all the time but ...


5

I can think of a few questions about this implementation that I would want to know before continuing: What happens if, somehow, microsoft creates an object with the exact same name in a new version? If databases are migrated to a new server, how will anyone know that there's something in master to migrate? What happens if your objects corrupt the master ...


5

Actually it will work. Most SSB verbs accept parameters for they arguments (except queuename for RECEIVE, of course). The parameters are of type sysname: Create Procedure SendJoinRequest @from sysname, @to sysname, @contract sysname, @messageType sysname, @body XML AS Declare @dialog_handle UNIQUEIDENTIFIER BEGIN TRANSACTION BEGIN DIALOG ...


5

Read the Mysterious Notification for an explanation on how it works. If you see the query executed every 10ms it means your application is running it every 10ms. My 8 ball is telling me your code does not check the notification EventArgs and is blindly resubmitting the query despite the Info being of the value Invalid, indicating a query than cannot be ...


5

This is an open question with no clear choice. YMMV so you have to test. Here is my opinion: Having one queue to handle everything is a good choice if you want to be able to control the number of activated tasks, as there is no global max_queue_readers. Other than that, I don't see many advantages. One could argue that one single activated proc is easier to ...


4

A good place to start could be Getting Started (Service Broker)


4

From what I understand the limitation only applies when you attempt to send messages outside of the instance. Sending messages within the same instance is not limited by using Express. See the last reply in the following link: http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/3b03a610-8159-4be8-b2d8-e2abb4ba1225 I hope this helps you.


4

Over the past several months, I have been working with Microsoft's Product Support team, and they have acknowledged two bugs in SQL Server 2012 in relation to this issue. They will be releasing patches for those bugs as part of the next service pack for SQL Server 2012.


4

I dug into this and it looks like the SQL Server team went out of their way to prevent internal table stats from being exposed through DMFs and DMVs. I can't say I blame them, as the implementation of things like queues is always subject to change, but it doesn't help you out with your problem. I can see why in this case it's really difficult to balance ...


4

Because RECEIVE is basically a DELETE and as such has a query plan, it must obey the same restrictions SELECT/INSERT/DELETE/UPDATE statements have, specifically the restrictions that the object it acts on must be known at compile time, not at execution time. The only option is to use dynamic SQL, with all the blessings and pitfalls that follow. You could ...


4

The original design called for separate enqueue_enabled (allow SEND) and dequeue_enabled (allow RECEIVE). The added complexity (as SSB wouldn't already be complex enough!) did not warrant the benefits, so in the end it was settled for a less granular 'queue enabled'. The metadata though was intentionally left split in order to enable the separation in ...


4

On SQL Server 2008 R2, if I execute a WAITFOR(RECEIVE), then run DBCC OPENTRAN, it shows the transaction as active, even in the absence of any prior updates.


4

Even in Management Studio that ships with SQL Server 2012 (BTW please stop using SSMS 2005 to manage 2008+ instances), the Generate Scripts option will not offer Service Broker objects, whether you select "script all" or select individual objects. You'll need to use other methods - SMO, PowerShell, or right-clicking within the Service Broker node of Object ...


4

Your best bet is to use Transactional Replication. T-Rep is meant to be suited in your scenario as you want to replicate a table from server A to server B. No need to run a store procedure every min (unless you are doing something specific with these SP's which you have not highlighted in your question). Note: Only when you generate an initial snapshot in ...


4

When you restore a database, TRUSTWORTHY is automatically set to OFF. For Service Broker, if you don't use encryption and do use cross-database message transmission, TRUSTWORTHY needs to be set to ON. Try ALTER DATABASE MyDB SET TRUSTWORTHY ON ...for all restored databases involved with Broker.


3

The 'client only' means that two Express instances cannot communicate directly. They have to have a Standard instance in between acting as a forwarder. Express instances can both generate and consume messages. Messages originating on Express instances can be received on Standard or higher instances. Messages originating on Standard or higher instance can ...


3

is there a way I can send messages directly to the Database Mail queue using Service Broker When you invoke sp_send_dbmail that is exactly what is happening. I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to send e-mails to operators for each message on the queue Then do just that. Create an activated stored procedure on the ...


2

Service Broker conversations are between services, not between queues. You send from a service, to a service. A conversation is tied to exactly a pair of services and cannot change. The same service can participate in any number conversations, with any number of peer services. If you want to send messages to multiple peers, you need to start multiple ...


2

This is a working sample receive procedure that is re-usable over different queues. ALTER PROCEDURE [dbo].[zBroker_OnReceive_WFItemsUpdated_Queue] AS BEGIN SET NOCOUNT ON; DECLARE @h UNIQUEIDENTIFIER; DECLARE @messageType int; DECLARE @Packet VARCHAR(50); DECLARE @MyId ...


2

I wanted to add that the database is a restore from a SQL Server 2005 When you restore a database the Service Broker in it gets disabled and must be explicitly enabled (there are reasons why this is necessary, but I won't go into them). If you restored the DB and then set up the AG w/o enabling broker first, you have set up the AG w/o the Service Broker ...


2

You do not have to, but is a good practice. You must be aware that your 'from' service will also get messages in it's queue, even if you never send any. There are system messages, most obvious ones being the EndDialog and Error messages. You have to handle at least those. So your code that services the queue must be able to properly handle the messages sent ...


2

Sort of. You can see though sys.dm_exec_requests executing a RECEIVE statement (including a WAITFOR RECEIVE). By simply peeking into the the currently executing text sys.dm_exec_sql_text(sql_handle) between the statement_start_offset and statement_end_offset you can see if the statement is RECEIVE or not (with some parsing...). Figuring out if an active ...


2

Contention will be the biggest problem. A conversation guarantees order but in order for such guarantee to be give the SEND verb must lock the conversation handle used until the end of transaction (or during the statement if no explicit transactions is used). In effect that means that only one transaction can send a bid on product A at any time. Whether this ...



Only top voted, non community-wiki answers of a minimum length are eligible