Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 13 down vote accepted

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.

share|improve this answer
    
Would WAITFOR (...) TIMEOUT 3600000 solve the problem? E.g. release hourly. –  AngryHacker Dec 17 '12 at 19:34
1  
Your log will grow a lot in one hour. WAITFOR (REC EIVE) is meant for intervals like 5 seconds... –  Remus Rusanu 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. –  Remus Rusanu Dec 17 '12 at 19:40
    
I don't understand your last comment. The transaction is active because I issued a WAITFOR (RECEIVE... Could you expand? Perhaps, I misunderstood. –  AngryHacker Dec 17 '12 at 19:56
7  
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 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.

share|improve this answer
1  
Correct, the WAITFOR creates a savepoint internally and this triggers the log write so it does pin the log in place. –  Remus Rusanu Dec 25 '12 at 13:47
    
Thanks for the clarification. –  Nigel Pattinson Dec 27 '12 at 19:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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