Tag Info

Hot answers tagged

15

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

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 ...


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

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


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

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

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 ...


5

Looks like you're using a tool not capable of identifying benign waits. See Wait statistics, or please tell me where it hurts. There is no impact, just bad monitoring. I recommend you read Filtering out benign waits. The engine has certain background tasks and when these background tasks don't have any work they simply sit idle, waiting for work. while ...


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

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

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

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.


4

No. RECEIVE constructs a query AST that cannot be constructed with SELECT. RECEIVE has to guarantee EOIO and conversation group locking, SELECT does not. Service Broker has a built-in 'peek': GET CONVERSATION GROUP. This will lock the next available conversation group, on which you are guaranteed to get a message if you issue a RECEIVE within the same ...


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 ...


3

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 ...


3

No, activation stored procedures are called "in a vacuum" so they don't allow passing parameters (there's "nothing" calling it) nor is there even a user context in which to execute, which is why the EXECUTE AS syntax exists and is required for activation. If you need to abstract the logic, it can be separated into two procedures. The activation procedure is ...


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

We've had 50+ machines doing this all day every day in SQL Server 2005. Been doing it for 3+ years without issue.



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