WAITFOR in External activator does not allow to shrink logfile

Question

I am using External activator to notify that a message is available in the Queue to process. My External app picks up the message and process it successfully. Everything works fine.

The issue is that due to the WAITFOR that is indefinate (without timeout) in the activator, I am not able to shrink log file.
I backed up my database and ran Shrink database using SSMS. The log file did not shirnk. So I tried 

DBCC SHRINKFILE(db_Log, 1)
BACKUP LOG xw1_1_Core WITH TRUNCATE_ONLY
DBCC SHRINKFILE(db_Log, 1)

But could not shrink the file.
I think the reason is because of the log_reuse_wait, log_reuse_wait_desc in sys.databases is set to 4 (ACTIVE_TRANSACTION), and that's due to the active transaction by WAITFOR in Activator.

Has anyone come across similar problem and if anyone has a solution or suggest a solution to shrink the file. I would prefer that I don't have to stop the Activator service (i mean WAITFOR).

Not sure whether this question belong to Service broker or SQL server engine.

thanks in advance.

Answer 1

You must add a timeout. Even something like a 1 minute timeout would to the trick.
WAITFOR starts by creating a transaction savepoint and then the whole time it waits the transaction is kept open, so the log cannot be truncated.

---

http://rusanu.com

Answer 2

Thanks for you reply, Remus.

I thought of adding the TIMEOUT, large enough like 1 minute suggested by you. But due to timeout, i think i will not be able to achive 24x7 availability of the queue for responding to the event. Could you please give more details how TIMEOUT will help ?

To give you how my code works

while (true)
{
    WAITFOR ()  // as per suggestion add TIMEOUT 60000
   

    // add a delay for 5 or 10 sec, for next WAITFOR. If i dont add this next waitfor will start immediately what will not allow me to shrinkfile.
}
    
the outer while will help me achive the 24x7 responding capability and the 10 sec break for every 1 minute waitfor. I have 10 sec window to achive my shrinkfile. Not sure that will be enough to achive shrinkfile. Please comment on this point.
Still to say, that i am not able to achive real-time 24x7 response to events. Is there any way i can achive 24x7 real-time response and also shrinkfile.

Thanks for your time.

Answer 3

jcp169,

Are you talking about a WAITFOR in your own service app or the external activator? The WAITFOR in the official Service Broker External Activator already has a timeout of 3 seconds, so there should be no problem there.

Answer 4

I am talking about external activator code for SQL 2005 which i believe was not official code released by SQL team. May be the recently released External Activator has a timeout of 3 sec. 

Answer 5

WAITFOR does not prevent the log shrink operation, it prevents the log from shrinking past the point when the transaction was saved. So if you periodically exit the WAITFOR and commit you allow the shrink. The log shrink operation can occur at any time and you don't have to place any 10 second window.

---

http://rusanu.com

Answer 6

I am new to this Extrenal activator concept.

Can you please provide the steps or some sample code to run the Windows service/Windows application using External Activator if you have.

I am unable to recive the queue messages and i am unable to run the external application.

Can you please provide some guide lines to implement the External activator?

Thanks in advance,
Manoj

Answer 7

Manoj,

I have replied to your question on another thread. Please see here: http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/cdedfa58-8952-45b4-85aa-de331166fc27 .

Thanks,
Pawel