A little update about what I have found out about this "problem".

The issue seems to be related to the activation procedure and the use of WAITFOR(RECEIVE...) TIMEOUT n

When a new message arrives at a queue, the activation procedure is executed. This procedure contains an outer WHILE loop so that if there are multiple messages in the queue, they are all RECEIVEd and processed within the same batch. This prevents the activation procedure from having to execute once per message.

Within the WHILE loop there is a WAITFOR(RECEIVE top(1) ...) TIMEOUT 5000 that will wait for 5 seconds for new messages in the queue. If a message is received then it is processed, and if no message is received after five seconds the WHILE loop is exited and the procedure quits execution.

What I found out is that when the timeout in the WAITFOR is reached, then the "Broker Transaction Rollbacks" counter is incremented with one.

This only happens when the procedure containing the WAITFOR is executed using queue activation. If you disable activation and run the procedure manually, then the counter is NOT incremented.

I asked myself if there was something wrong in the procedure that caused it to fail when using activation, so I created a log table and put an INSERT into this table in the CATCH section of the procedure (after any COMMIT or ROLLBACK of the transaction so that the log record doesn't get rolled back 😛 ), but nothing is logged, as I expected, since all messages are processed successfully.

I have no idea why a WAITFOR(RECEIVE...) TIMEOUT that reaches it timeout limit will increment the "Broker Transaction Rollbacks" when run by activation, but not when run manually.

All I know is that everything is working fine, so I'm not going to put more effort into trying to solve it.

FYI, I'm running SQL Server 2005 SP3.