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: ldusadev at kestrok dot com (Rich Bailey)
Subject: Re: transaction log problems
Date: Dec 6 1997 2:04AM

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
           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 was advised against a dump trans since I might affect other users. Any
further ideas welcome.

>Rich Bailey wrote:
>> Hi all:
>> I have a perl program that does
>> $ref = $A->ct_sql("delete $database_name..BILLHIST where datediff(mm,
>> BILLING_DATE, getdate()) >= 14");)
>> Sometimes I get a transaction log problem, because the delete pulls too many
>> rows. The process hangs because the transaction log becomes suspended. The
>> log filled up I guess.
>> I then added a callback to handle the server errors and waited for a
>> tranaction log message.
>> ct_callback(CS_SERVERMSG_CB, "srv_cb");
>> When I saw one then I tried cancelling the delete with ct_cancel. However,
>> it never seemed to work. So I just decided to quit the program and print an
>> error to a log file.
>> It would be nice if I could have cancelled the delete then have the program
>> continue on. Does anyone have ideas on how to deal with transaction log
>> problems they can share. Would it be possible to figure out a way to make
>> sure the transaction log was large enough before I try the delete??
>What you really want to do is to issue a 'dump tran' when the 
>transaction log fills up and suspends your operation.
>And you may want to split your delete into smaller chunks to avoid
>(or at least limit) the transaction log overflow. This can be done
>by using setting ROWCOUNT, and looping until the number of rows
>affected by the command is 0.
>Michael Peppler       -||-  Data Migrations Inc.
>  -||-