CLR vs Long Transactions...
User reported Long Transaction errors with CLR-based replication despite sufficient log space, caused by forcing log switches every 5 minutes to keep secondary systems current. Suggested solution: switch to HDR or RSS instead, which doesn't require forced log switches. User declined, preferring CLR's resilience with poor networking conditions, leaving the issue unresolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Everyone - hoping someone can explain some behaviour we're seeing here.. We're using CLR in a few places for basic replication/DR functionality. Several of these sites have quite low transaction rates, so to ensure that our secondary system isn't too far behind the primary, our log transfer scripts actually force a log switch. Due to the timing of this replication (every 5 minutes), this means that we actually only use a fraction of each logical log before it's switched and backed up. However during periods of high activity, we're getting Long Transaction errors, despite the fact that the total available log space is easily sufficient to cope with the transactions we're seeing. It's almost like the forced switches of the logs is artificially reducing our available log space? I originally thought this would be ok, because even if we are only using part of each log file they get backed up straight away and should be available for re-use, but that doesn't seem to be the case? Regards, Tristan [Pronto Hosted Services] Tristan Ball - Hosted Services Manager VIC Pronto Hosted Services 20 Lakeside Drive, Burwood East, VIC 3151 Phone: +61 3 9887 7770 | Email: tristanb@pronto.com.au<mailto:tristanb@pronto.com.au> Mobile: +61 408 397 473 For PHS helpdesk support, please email phs@pronto.com.au<mailto:phs@pronto.com.au> For urgent after hours support phone: 1800 622 556 ---Legal Notice--- The email message and any attachments are confidential and subject to copyright. If you are not the intended recipient, any use, interference with, disclosure or copying of this material is unauthorised and prohibited. No part may be reproduced, adapted or transmitted without the written permission of the copyright owner. If you have received this email in error, please immediately advise the sender by return email and delete the message from your system. Before opening or using attachments, check for viruses and defects. Our liability is limited to re-supplying any affected attachments.
Switch to HDR, that's what it is for! Or RSS of the servers are too far apart. Then you can stop switching logs every 5 minutes. Art On Nov 24, 2011 8:00 PM, "Tristan Ball" <tristanb@pronto.com.au> wrote: > Hi Everyone - hoping someone can explain some behaviour we're seeing here.. > > We're using CLR in a few places for basic replication/DR functionality. > Several of these sites have quite low transaction rates, so to ensure that > our > secondary system isn't too far behind the primary, our log transfer scripts > actually force a log switch. > > Due to the timing of this replication (every 5 minutes), this means that we > actually only use a fraction of each logical log before it's switched and > backed up. > > However during periods of high activity, we're getting Long Transaction > errors, despite the fact that the total available log space is easily > sufficient to cope with the transactions we're seeing. > > It's almost like the forced switches of the logs is artificially reducing > our > available log space? I originally thought this would be ok, because even > if we > are only using part of each log file they get backed up straight away and > should be available for re-use, but that doesn't seem to be the case? > > Regards, > > Tristan > > [Pronto Hosted Services] > > Tristan Ball - Hosted Services Manager VIC > Pronto Hosted Services > 20 Lakeside Drive, Burwood East, VIC 3151 > Phone: +61 3 9887 7770 | Email: > tristanb@pronto.com.au<mailto:tristanb@pronto.com.au> > Mobile: +61 408 397 473 > > For PHS helpdesk support, please email > phs@pronto.com.au<mailto:phs@pronto.com.au> > For urgent after hours support phone: 1800 622 556 > > ---Legal Notice--- > The email message and any attachments are confidential and subject to > copyright. If you are not the intended recipient, any use, interference > with, > disclosure or copying of this material is unauthorised and prohibited. No > part > may be reproduced, adapted or transmitted without the written permission of > the copyright owner. If you have received this email in error, please > immediately advise the sender by return email and delete the message from > your > system. Before opening or using attachments, check for viruses and defects. > Our liability is limited to re-supplying any affected attachments. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba4d34c33f704b286faad
Thanks, but it's the general feel around here that CLR is the most resilient option when faced with somewhat less than perfect networking conditions. The fact that the primary "doesn't know about the secondary" essentially means there's no failure mode where a failure of the RSS or HDR function can cause issues on the primary. This long transaction issue not-withstanding of course. :-) Regards, Tristan Tristan Ball - Hosted Services Manager VIC Pronto Hosted Services 20 Lakeside Drive, Burwood East, VIC 3151 Phone: +61 3 9887 7770 | Email: tristanb@pronto.com.au Mobile: +61 408 397 473 For PHS helpdesk support, please email phs@pronto.com.au For urgent after hours support phone: 1800 622 556 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Friday, 25 November 2011 2:45 PM To: ids@iiug.org Subject: Re: CLR vs Long Transactions... [25472] Switch to HDR, that's what it is for! Or RSS of the servers are too far apart. Then you can stop switching logs every 5 minutes. Art On Nov 24, 2011 8:00 PM, "Tristan Ball" <tristanb@pronto.com.au> wrote: > Hi Everyone - hoping someone can explain some behaviour we're seeing here.. > > We're using CLR in a few places for basic replication/DR functionality. > Several of these sites have quite low transaction rates, so to ensure > that our secondary system isn't too far behind the primary, our log > transfer scripts actually force a log switch. > > Due to the timing of this replication (every 5 minutes), this means > that we actually only use a fraction of each logical log before it's > switched and backed up. > > However during periods of high activity, we're getting Long > Transaction errors, despite the fact that the total available log > space is easily sufficient to cope with the transactions we're seeing. > > It's almost like the forced switches of the logs is artificially > reducing our available log space? I originally thought this would be > ok, because even if we are only using part of each log file they get > backed up straight away and should be available for re-use, but that > doesn't seem to be the case? > > Regards, > > Tristan > > [Pronto Hosted Services] > > Tristan Ball - Hosted Services Manager VIC Pronto Hosted Services > 20 Lakeside Drive, Burwood East, VIC 3151 > Phone: +61 3 9887 7770 | Email: > tristanb@pronto.com.au<mailto:tristanb@pronto.com.au> > Mobile: +61 408 397 473 > > For PHS helpdesk support, please email > phs@pronto.com.au<mailto:phs@pronto.com.au> > For urgent after hours support phone: 1800 622 556 > > ---Legal Notice--- > The email message and any attachments are confidential and subject to > copyright. If you are not the intended recipient, any use, > interference with, disclosure or copying of this material is > unauthorised and prohibited. No part may be reproduced, adapted or > transmitted without the written permission of the copyright owner. If > you have received this email in error, please immediately advise the > sender by return email and delete the message from your system. Before > opening or using attachments, check for viruses and defects. > Our liability is limited to re-supplying any affected attachments. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba4d34c33f704b286faad ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
RSS cannot hang the primary, fwiw. Art On Nov 24, 2011 11:50 PM, "Tristan Ball" <tristanb@pronto.com.au> wrote: > Thanks, but it's the general feel around here that CLR is the most > resilient > option when faced with somewhat less than perfect networking conditions. > The > fact that the primary "doesn't know about the secondary" essentially means > there's no failure mode where a failure of the RSS or HDR function can > cause > issues on the primary. > > This long transaction issue not-withstanding of course. :-) > > Regards, > > Tristan > > Tristan Ball - Hosted Services Manager VIC > Pronto Hosted Services > 20 Lakeside Drive, Burwood East, VIC 3151 > Phone: +61 3 9887 7770 | Email: tristanb@pronto.com.au > Mobile: +61 408 397 473 > > For PHS helpdesk support, please email phs@pronto.com.au > For urgent after hours support phone: 1800 622 556 > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Friday, 25 November 2011 2:45 PM > To: ids@iiug.org > Subject: Re: CLR vs Long Transactions... [25472] > > Switch to HDR, that's what it is for! Or RSS of the servers are too far > apart. > Then you can stop switching logs every 5 minutes. > > Art > On Nov 24, 2011 8:00 PM, "Tristan Ball" <tristanb@pronto.com.au> wrote: > > > Hi Everyone - hoping someone can explain some behaviour we're seeing > here.. > > > > We're using CLR in a few places for basic replication/DR functionality. > > Several of these sites have quite low transaction rates, so to ensure > > that our secondary system isn't too far behind the primary, our log > > transfer scripts actually force a log switch. > > > > Due to the timing of this replication (every 5 minutes), this means > > that we actually only use a fraction of each logical log before it's > > switched and backed up. > > > > However during periods of high activity, we're getting Long > > Transaction errors, despite the fact that the total available log > > space is easily sufficient to cope with the transactions we're seeing. > > > > It's almost like the forced switches of the logs is artificially > > reducing our available log space? I originally thought this would be > > ok, because even if we are only using part of each log file they get > > backed up straight away and should be available for re-use, but that > > doesn't seem to be the case? > > > > Regards, > > > > Tristan > > > > [Pronto Hosted Services] > > > > Tristan Ball - Hosted Services Manager VIC Pronto Hosted Services > > 20 Lakeside Drive, Burwood East, VIC 3151 > > Phone: +61 3 9887 7770 | Email: > > tristanb@pronto.com.au<mailto:tristanb@pronto.com.au> > > Mobile: +61 408 397 473 > > > > For PHS helpdesk support, please email > > phs@pronto.com.au<mailto:phs@pronto.com.au> > > For urgent after hours support phone: 1800 622 556 > > > > ---Legal Notice--- > > The email message and any attachments are confidential and subject to > > copyright. If you are not the intended recipient, any use, > > interference with, disclosure or copying of this material is > > unauthorised and prohibited. No part may be reproduced, adapted or > > transmitted without the written permission of the copyright owner. If > > you have received this email in error, please immediately advise the > > sender by return email and delete the message from your system. Before > > opening or using attachments, check for viruses and defects. > > Our liability is limited to re-supplying any affected attachments. > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --e89a8f3ba4d34c33f704b286faad > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba4d3aab97004b2895e9d
Immediately backing up your log doesn't automatically make it available for re-use. A long transaction is a function of how many logical logs a transaction spans (stays open) with respect to the amount of logical logs you have defined. In your situation every time you do a log switch you are advancing the current log faster than it would if it was left to fill completely thereby increasing the probababilty of a long transaction error occurring. Adding more logical logs or increasing your high water marks will reduce the chances of this error happpening.
The general feeling is wrong :) Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Tristan Ball Sent: Thursday, November 24, 2011 10:50 PM To: ids@iiug.org Subject: RE: CLR vs Long Transactions... [25473] Thanks, but it's the general feel around here that CLR is the most resilient option when faced with somewhat less than perfect networking conditions. The fact that the primary "doesn't know about the secondary" essentially means there's no failure mode where a failure of the RSS or HDR function can cause issues on the primary. This long transaction issue not-withstanding of course. :-) Regards, Tristan Tristan Ball - Hosted Services Manager VIC Pronto Hosted Services 20 Lakeside Drive, Burwood East, VIC 3151 Phone: +61 3 9887 7770 | Email: tristanb@pronto.com.au Mobile: +61 408 397 473 For PHS helpdesk support, please email phs@pronto.com.au For urgent after hours support phone: 1800 622 556 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Friday, 25 November 2011 2:45 PM To: ids@iiug.org Subject: Re: CLR vs Long Transactions... [25472] Switch to HDR, that's what it is for! Or RSS of the servers are too far apart. Then you can stop switching logs every 5 minutes. Art On Nov 24, 2011 8:00 PM, "Tristan Ball" <tristanb@pronto.com.au> wrote: > Hi Everyone - hoping someone can explain some behaviour we're seeing here.. > > We're using CLR in a few places for basic replication/DR functionality. > Several of these sites have quite low transaction rates, so to ensure > that our secondary system isn't too far behind the primary, our log > transfer scripts actually force a log switch. > > Due to the timing of this replication (every 5 minutes), this means > that we actually only use a fraction of each logical log before it's > switched and backed up. > > However during periods of high activity, we're getting Long > Transaction errors, despite the fact that the total available log > space is easily sufficient to cope with the transactions we're seeing. > > It's almost like the forced switches of the logs is artificially > reducing our available log space? I originally thought this would be > ok, because even if we are only using part of each log file they get > backed up straight away and should be available for re-use, but that > doesn't seem to be the case? > > Regards, > > Tristan > > [Pronto Hosted Services] > > Tristan Ball - Hosted Services Manager VIC Pronto Hosted Services > 20 Lakeside Drive, Burwood East, VIC 3151 > Phone: +61 3 9887 7770 | Email: > tristanb@pronto.com.au<mailto:tristanb@pronto.com.au> > Mobile: +61 408 397 473 > > For PHS helpdesk support, please email > phs@pronto.com.au<mailto:phs@pronto.com.au> > For urgent after hours support phone: 1800 622 556 > > ---Legal Notice--- > The email message and any attachments are confidential and subject to > copyright. If you are not the intended recipient, any use, > interference with, disclosure or copying of this material is > unauthorised and prohibited. No part may be reproduced, adapted or > transmitted without the written permission of the copyright owner. If > you have received this email in error, please immediately advise the > sender by return email and delete the message from your system. Before > opening or using attachments, check for viruses and defects. > Our liability is limited to re-supplying any affected attachments. > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba4d34c33f704b286faad **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. _____ avast! Antivirus <http://www.avast.com>; : Outbound message clean. Virus Database (VPS): 111125-0, 11/25/2011 Tested on: 11/25/2011 12:52:48 PM avast! - copyright (c) 1988-2011 ALWIL Software.