Up Prev Next
From: Ken Galbraith <galb at erols dot com>
Subject: Re: transaction log problems
Date: Dec 3 1997 2:10AM
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??
> Thanks a lot for any ideas.
Seems to me that you should get together with your dba to either expand
your transaction log as needed or to coordinate the timing of your
maintenance program and the transaction log dumps. If your program runs
after a dump and you still fill up the log, then the log needs to be
expanded or your program needs to do its deletes in batches.
I don't think the program should try to guess whether it is safe to
run. What if it is never safe? Well, you would then yell at the dba to
make the log bigger. So why waste time. Do it now.
My 2 cents,