Up Prev Next
From: Michael Peppler <mpeppler at mbay dot net>
Subject: Re: transaction log problems
Date: Dec 13 1997 1:10AM
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.
email@example.com -||- http://www.mbay.net/~mpeppler