Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"Broker Transaction Rollbacks" Performance Counter Expand / Collapse
Author
Message
Posted Monday, September 12, 2011 7:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,842, Visits: 13,372
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
Post #1173429
Posted Monday, September 12, 2011 8:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 1,890, Visits: 3,472
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.
Post #1173448
Posted Monday, September 12, 2011 8:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,842, Visits: 13,372
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
Post #1173507
Posted Tuesday, September 13, 2011 2:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 1,890, Visits: 3,472
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.
Post #1173941
Posted Tuesday, September 13, 2011 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,842, Visits: 13,372
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
Post #1173979
Posted Tuesday, September 13, 2011 4:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 1,890, Visits: 3,472
Rusanu should know whats causing it. After all he designed most of the Service Broker architecture.
Post #1173982
Posted Thursday, September 15, 2011 4:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 1,890, Visits: 3,472
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.
Post #1175537
Posted Thursday, September 15, 2011 1:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,842, Visits: 13,372
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
Post #1176021
Posted Thursday, September 15, 2011 1:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 1,890, Visits: 3,472
Looking forward to read Rusanu's explaination (if he approves that you post it).
Post #1176031
Posted Friday, September 16, 2011 9:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,842, Visits: 13,372
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
Post #1176480
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse