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?

Thomas Stringer
  • 42,434
  • 9
  • 120
  • 155
AngryHacker
  • 1,961
  • 5
  • 22
  • 33

2 Answers2

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.

Remus Rusanu
  • 52,054
  • 4
  • 96
  • 172
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.

Nigel Pattinson
  • 51
  • 1