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 DBAs tell me that since the addition, the log sizes have gone through the roof. Could this be correct? Or should I be looking elsewhere?


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. Just issue a RECIEVe and be done, let the activation mechanism loop for you (it does) and don't WAITFOR, just plain RECEIVE.

The WAITFOR flavor of RECEIVE creates a savepoint internally. This generates log (at least 3 log records) and does indeed pin the log in place while waiting. Having a long WAITFOR timeout (or worse, an infinite one) would be a very bad practice.

  • 1
    Would WAITFOR (...) TIMEOUT 3600000 solve the problem? E.g. release hourly. Dec 17 '12 at 19:34
  • 2
    Your log will grow a lot in one hour. WAITFOR (REC EIVE) is meant for intervals like 5 seconds... Dec 17 '12 at 19:39
  • 1
    You should also investigate why your transaction is actually active (has written log). The typical Service Broker pattern dow not issue any write before the RECEIVE. Dec 17 '12 at 19:40
  • 1
    I don't understand your last comment. The transaction is active because I issued a WAITFOR (RECEIVE... Could you expand? Perhaps, I misunderstood. Dec 17 '12 at 19:56
  • 8
    begin transaction; waitfor(receive...) will not generate any log records (will not 'activate' the transaction) while waiting, and thus will not pin the log. Only begin transaction;[insert|update|delete];waitfor(receive...) will cause the transaction to 'activate' (generate log records) and thus will actually pin the log while waiting. Dec 17 '12 at 20:49

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.

  • 2
    Correct, the WAITFOR creates a savepoint internally and this triggers the log write so it does pin the log in place. Dec 25 '12 at 13:47
  • @RemusRusanu doesn’t that contradict your earlier comments on the other answer?
    – binki
    Aug 9 '16 at 2:26
  • @binki that comment refers to SQL Server 2005. This is for 2008 R2. They behave differently with regard to this issue, if I remember correctly. Aug 9 '16 at 5:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.