17

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?

Improve this question

2 Answers 2

Reset to default
18

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.

Improve this answer
5
  • 1
    Would WAITFOR (...) TIMEOUT 3600000 solve the problem? E.g. release hourly.
    – AngryHacker
    Commented Dec 17, 2012 at 19:34
  • 2
    Your log will grow a lot in one hour. WAITFOR (REC EIVE) is meant for intervals like 5 seconds...
    – Remus Rusanu
    Commented Dec 17, 2012 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.
    – Remus Rusanu
    Commented Dec 17, 2012 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.
    – AngryHacker
    Commented Dec 17, 2012 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.
    – Remus Rusanu
    Commented Dec 17, 2012 at 20:49
5

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.

Improve this answer
3
  • 2
    Correct, the WAITFOR creates a savepoint internally and this triggers the log write so it does pin the log in place.
    – Remus Rusanu
    Commented Dec 25, 2012 at 13:47
  • @RemusRusanu doesn’t that contradict your earlier comments on the other answer?
    – binki
    Commented Aug 9, 2016 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.
    – Remus Rusanu
    Commented Aug 9, 2016 at 5:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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