# Saturday, 24 July 2010

Sometimes one is confronted with forgetting an option... It all started with a database that had a single data file of 1.8 GB and a transaction log more than 6 times the size of that. Usually that would be a database in full recovery model and no transaction log backups. This database was in simple recovery model though and according to the Disk Usage report, 99.99% of the transaction log was free. So a DBCC SHRINKFILE should solve the problem, however DBCC SHRINKFILE solved nothing, the log remained at about 11 GB.

A query on sys.databases learned that this particular database had ACTIVE_TRANSACTION for log_reuse_wait_desc. A peek in Activity Monitor then showed that there was an open transaction for the database; a DELETE task with a huge wait time and the BROKER_RECEIVE_WAITFOR wait type.

Then it occurred to me this is the database that used to have a deadlock problem. After implementing some procedure fixes, there haven’t been any deadlocks for over a half year and neither has the server (or service) been shutdown in between.

The stored procedure on the queue does a receive, but no timeout associated to it. Effectively this means that the receive will wait inside the transaction, until a messages comes into the queue. That hasn’t happened in the last six months. The resolution is simple, add the TIMEOUT option to the RECEIVE and the procedure will stop when there wasn’t any work to be done for the specified amount of time (in milliseconds). For the code, see the post “Deadlock Alerts Through Event Notification”, which has been updated with the TIMEOUT option.

After altering the procedure, kill the spid. Service Broker will restart, but now with the TIMEOUT option in the procedure and stop after the specified amount of time has elapsed without messages arriving in the queue.

Wednesday, 11 August 2010 16:18:20 (W. Europe Daylight Time, UTC+02:00)
Hi,

I too had a huge waittime for BROKER_RECEIVE_WAITFOR . Have few questions on this. When does the procedure get activated ? When does the timeout actually start ?

Thanks,

Durga
Wednesday, 11 August 2010 16:56:00 (W. Europe Daylight Time, UTC+02:00)
Hi Durga,
Activation is described in http://msdn.microsoft.com/en-us/library/ms171601.aspx. Simplified in my case before adding TIMEOUT to the RECEIVE statement, it occurred once (when the last message in the queue was processed) and continued running until the next message would come into the queue. With the TIMEOUT added to RECEIVE, it starts the timer once all work is done (queue empty) and will wait for that amount of milliseconds for new messages to arrive. Either a new message arrives in the queue before the timeout elapsed the procedure processes the message (and the timer gets reset). Or the procedure stops. When a new message arrives in this case, service brokers sees activation is needed and starts the procedure. Hope this helps.
Stan
Comments are closed.