Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at mbay dot net>
Subject: Re: transaction log problems
Date: Dec 13 1997 1:10AM

Rich Bailey wrote:
> Hi all:
> Mike P. suggested using a loop and deleting in batches to eliminate a
> transaction log problem I was experiencing. However, using the code below
> didn't seem to work. I got the same transaction log filled problem. Only if
> I reduce the timeperiod am I able to run it without bombing.
>     $A->ct_sql("use $database_name
>            declare \@count int
>            select \@count = $rt_cnt
>            set rowcount 5000
>            while (\@count > 0) begin
>              delete from $database_name..RATED
>                where datediff(mm, POSTED_DATE, getdate()) >= $backmnt
>              select \@count = \@count - 5000
>            end
>            set rowcount 0");
> I guess I could ask System Support to increase the transaction logs for
> those databases giving me problems. But you would think there'd be an easier
> and more reliable way to do this.

I don't know if you've resolved this, but I would run it in a loop
from perl, thus giving the server some time to actually do the
log truncation on the checkpoint between deletes, and maybe also
reduce the number of rows you delete in each go.

Michael Peppler       -||-  Data Migrations Inc.  -||-