PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: ldusadev at kestrok dot com (Rich Bailey)
Subject: Re: transaction log problems
Date: Dec 16 1997 2:11AM

>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
>-- 
>Michael Peppler       -||-  Data Migrations Inc.
>mpeppler@datamig.com  -||-  http://www.mbay.net/~mpeppler
>
>
Michael:
Thanks. Yep. I put a dump trans just before the delete statement and it
works. Deleted 4 million, 2 hundred thousand rows in about 2hrs. 20 mins.
Not bad!
Thanks again
Rich