Discussion:
log full bcp jobs rollback
(too old to reply)
unknown
2009-12-18 16:27:33 UTC
Permalink
Adaptive Server Enterprise/12.5.3/EBF 12332
ESD#1/P/RS6000/AIX

Hi,

I have a log full in a db with trunc log on checkpoint on.
There are 3 bcp jobs that were in log suspend. I tried to
kill the jobs and now they are in "rollback" state & seem to
be accumulating I/O. I am wondering if they are really doing
anything or not? Also, not wanting to increase the size of
the log (developers mistake) I am trying to dump tran to
file (no space in log), dump with trunc_only (can't trunc
log) and finally dump with no_log. Does anyone know if the
rollback of the three bcp's will complete or will the full
log stop it eventually?

thx,
rick_806
Bret Halford
2009-12-18 18:33:29 UTC
Permalink
Post by unknown
Adaptive Server Enterprise/12.5.3/EBF 12332
ESD#1/P/RS6000/AIX
Hi,
I have a log full in a db with trunc log on checkpoint on.
There are 3 bcp jobs that were in log suspend. I tried to
kill the jobs and now they are in "rollback" state & seem to
be accumulating I/O. I am wondering if they are really doing
anything or not? Also, not wanting to increase the size of
the log (developers mistake) I am trying to dump tran to
file (no space in log), dump with trunc_only (can't trunc
log) and finally dump with no_log. Does anyone know if the
rollback of the three bcp's will complete or will the full
log stop it eventually?
thx,
rick_806
It should complete. Space was reserved in the log
to write out the type of log records ASE uses for
rollbacks (CLRs), the bcp rollbacks are presumably
writing those out.

Dump tran can only clear space up to the beginning of the
oldest open transaction; it won't be able to clear more
space until the oldest of these rollbacks finished
(the master..syslogshold table will show the oldest open
process in the database).

Going forward, you might consider using batches with
bcp (-b parameter for bcp). Each batch is committed
on its own, allowing the log to be truncated between them.

-bret

Loading...