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
>> 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.
>firstname.lastname@example.org -||- http://www.mbay.net/~mpeppler
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.