Up Prev Next
From: ldusadev at kestrok dot com (Rich Bailey)
Subject: Re: transaction log problems
Date: Dec 6 1997 2:04AM
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.
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.
>email@example.com -||- http://www.mbay.net/~mpeppler