Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"Broker Transaction Rollbacks" Performance Counter


"Broker Transaction Rollbacks" Performance Counter

Author
Message
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7009 Visits: 13559
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 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...)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
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.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7009 Visits: 13559
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... ;-)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
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.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7009 Visits: 13559
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).



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
Rusanu should know whats causing it. After all he designed most of the Service Broker architecture. :-)
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
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 :-P ), 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.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7009 Visits: 13559
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.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
Looking forward to read Rusanu's explaination (if he approves that you post it).
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7009 Visits: 13559
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.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search