Up Prev Next
From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: Re: transaction log problems
Date: Dec 2 1997 1:11AM
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