sybperl-l Archive

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

>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
>-- 
>Michael Peppler       -||-  Data Migrations Inc.
>mpeppler@datamig.com  -||-  http://www.mbay.net/~mpeppler
>
>