September 12, 2011 at 7:44 am
Hi,
I just had a look at the "Service Broker Statistics" Standard Report (Instance - > right click -> Reports -> Standard Reports -> Service Broker Statistics).
The number for "Broker Transaction Rollbacks" is rather high, but the standard test (using ALZDBA's[/url] great script) didn't show anything unusual (e.g. all queues are empty).
The description provided by MS is not really helpful ("The number of rolled-back transactions that contained DML statements related to Service Broker, such as SEND and RECEIVE.")
All messages are processed correctly as far as I can see. I'm not seeing a ROLLBACK TRANSACTION in either the SEND or the RECEIVE section.
Google didn't return much either.
Any hints what this message really means and how to react properly? (if any action is required in the first place...)
September 12, 2011 at 8:02 am
Are you sure that there are no rollbacks on initial BEGIN DIALOG CONVERSATION or SEND ON CONVERSATION? If the client, or whoever initializes the conversation, issues a rollback then this counter is incremented.
the following example will increase the counter by one, and if you add a SEND statement in the transaction, it will be incremented by two.
begin tran
declare @handle uniqueidentifier
begin dialog conversation @handle
from service [sender_service]
to service 'receiver_service'
on contract [contract]
rollback
You need to find out if the client (initiator) is doing any rollbacks.
September 12, 2011 at 8:46 am
Hi Nils,
thank you for your prompt reply!
Unfortunately, there's no transaction involved (neither in the SEND nor the RECEIVE block).
The concept is based on Rusanu's reusing-conversations script (link in the SQL code).
ALTER PROCEDURE [dbo].[myConversation]
(
@fromService SYSNAME,
@toService SYSNAME,
@onContract SYSNAME,
@messageType SYSNAME,
@message VARCHAR(MAX)
)
AS
BEGIN
-- source: http://rusanu.com/2007/04/25/reusing-conversations/
SET NOCOUNT ON;
DECLARE @handle UNIQUEIDENTIFIER;
DECLARE @counter INT;
DECLARE @error INT;
SELECT @counter = 1;
BEGIN TRY
-- Will need a loop to retry in case the conversation is
-- in a state that does not allow transmission
--
WHILE (1=1)
BEGIN
-- Seek an eligible conversation in [SessionConversations]
--
SELECT @handle = Handle
FROM [SessionConversations]
WHERE SPID = @@SPID
AND FromService = @fromService
AND ToService = @toService
AND OnContract = @OnContract;
IF @handle IS NULL
BEGIN
-- Need to start a new conversation for the current @@spid
--
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE @fromService
TO SERVICE @toService
ON CONTRACT @onContract
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@handle) TIMEOUT = 43200;
INSERT INTO [SessionConversations]
(SPID, FromService, ToService, OnContract, Handle)
VALUES
(@@SPID, @fromService, @toService, @onContract, @handle);
END;
-- Attempt to SEND on the associated conversation
--
SEND ON CONVERSATION @handle
MESSAGE TYPE @messageType
(@message);
SELECT @error = @@ERROR;
IF @error = 0
BEGIN
-- Successful send, just exit the loop
--
BREAK;
END
SELECT @counter = @counter+1;
IF @counter > 10
BEGIN
-- We failed 10 times in a row, something must be broken
--
INSERT INTO Error_Log(
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9)
SELECT
getdate(),
'myProc',
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE(),
'SEND on a conversation failed10x.' + @message +','+@@ERROR
BREAK;
END
-- Delete the associated conversation from the table and try again
--
DELETE FROM [SessionConversations]
WHERE Handle = @handle;
SELECT @handle = NULL;
END
END TRY
BEGIN CATCH
INSERT INTO Error_Log(
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9)
SELECT
getdate(),
'myProc',
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE(),
'SEND on a conversation failed. ' + @message +','+@@ERROR
END CATCH
END
I'm confident the SEND and RECEIVE prcedures do not contain any ROLLBACK command. But it might be the sprocs being called based on the RECEIVE command can raise a ROLLBACK. But since neither the SEND nor the RECEIVE block are wrapped in a transaction, this shouldn't affect the Service Broker handling. At least not from my point of view... 😉
September 13, 2011 at 2:05 am
I'm seeing the exact same thing as you do. "Broker Transaction Rollbacks" is increasing by a few thousand every day, but everything seems to be working fine. No users are complaining.
I will investigate what is causing this in our "shop", and I'll post my findings.
September 13, 2011 at 4:03 am
Thank you for the feedback.
I'm not sure if we're just "chasing the wind" here since I'm observing exactly the same (everything runs fine except the number increase).
If neither one of us (nor anyone who'll add some thoughts) can explain the behavior we might need to get the top gurus involved (e.g. Rusanu or Klaus Aschenbrenner).
September 13, 2011 at 4:06 am
Rusanu should know whats causing it. After all he designed most of the Service Broker architecture. 🙂
September 15, 2011 at 4:40 am
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.
September 15, 2011 at 1:03 pm
As per Remus Rusanu the increase of the counter is harmless and is no indication of a problem. Once I have permission I'll post his reply.
His explanation and what you've found seem to match. So I'd consider this case closed. Thank you again for putting this additional effort into it.
As a side note: I found a few db's where this counter is the only one having a number different than Zero on the Service Broker Statistics report.
September 15, 2011 at 1:38 pm
Looking forward to read Rusanu's explaination (if he approves that you post it).
September 16, 2011 at 9:18 am
Here's what Rusanu answered:
Some internal tasks follow a pattern of starting a transaction, looking for work to do, exit if no work. When they do so they are causing the transaction (which did no actual work) to rollback. The counter will increase, but is harmless.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply