NetNews Usenet Archive 1992 #31/NN_1992_31.iso/spool/comp/database/sybase/570

Internet Message Format | 1992-12-26 | 3.7 KB

Path: sparky!uunet!cs.utexas.edu!qt.cs.utexas.edu!yale.edu!spool.mu.edu!agate!ucbvax!mtxinu!sybase!ben
From: [e-mail] (Benjamin von Ullrich)
Newsgroups: comp.databases.sybase
Subject: Re: Recovering from a log segment full condition (why you may have to
Message-ID: <[e-mail]>
Date: 26 Dec 92 14:28:20 GMT
References: <[e-mail]> <dave.724703604@rincon>
Sender: [e-mail]
Organization: sybase, inc., emeryville-across-from-SF, ca
Lines: 64

In article <dave.724703604@rincon> [e-mail] (Dave Emde) writes:
>I've had the same problem myself.  Using dump tran database with no_log
>works will but there have been several instances where I dumped my transaction
>log only to find that I still received the "out of space in syslogs" message.
>
>In this case you need to shutdown and restart the server and then issue
>another dump with no_log.  This fixed my problem. If you have the Sybase
>Troubleshooting Guide they have a good writeup about this type of problem.

i think it's worthwhile to point out here why this is the case.
due to the sequential nature of the log, only the inactive portion of the log
may be truncated by any DUMP TRANSACTION command.  the inactive portion of the
log runs from the "beginning" to the page which has the BEGIN XACT record for
the oldest *active* (uncommitted) transaction.  pages which follow this oldest
active transaction in the log are considered active for the purposes of DUMP
TRANSACTION, since they may depend on changes made (yet to be committed or
still to be rolled back) by this transaction.  recovery (at LOAD TRAN or system
startup time) replays transactions as committed or rolled back in the
exact order in which they appear in the log, so portions appearing in
the log after an uncommitted transaction may not be removed.

the implication here is that given a large enough or long-running enough
transaction, one can hold up the entire log (from dumping, not from continued
logging!) while the transaction is still pending.  if your log fills up, and
you have a very old transaction that started at the beginning of the log,
no DUMP TRAN command can or will clear it until the transaction COMMITs or is
ROLLed BACK.

the only things you can do in this case are:
ALTER DATABASE to add more space to the log, hopefully allowing enough
space & time for your old transaction(s) to commit (find that user who
typed BEGIN TRAN ... UPDATE/INSERT/DELETE ...  and the went to lunch!).

KILL the long-running process/transaction.

shut down the sql server to terminate the long-running/old transaction.

these last two effectively terminate the transaction without a COMMIT, making
it get rolled back upon recovery.
this is a fairly drastic action to kill a process to clear a log.... if you
can issue a COMMIT TRAN in the open session, or type ^C to abort from the same,
please do so to achieve a cleaner and easier return to normal processing.

long-term, it is best to avoid long/log-intensive transactions to begin with:

>It recommends breaking up large deletes into smaller parts by adding a where
>clause.
[...]

this advice on use of a WHERE clause to break up DELETEs into chunks applies
the same to UPDATEs, and similarly for INSERT.

if your problem transaction is to delete all rows in a table, consider using the
TRUNCATE TABLE command.  this command uses a miniscule porportion of log vs.
a DELETE of all rows, as it merely logs the deallocation of pages for
the table, not the image of every row deleted.  for this reason, it is also
MUCH faster than DELETE for most good-sized tables.


--
..ben
------
Benjamin von Ullrich         only i do the talking here -- not my employer.
[e-mail]                   {pyramid,pacbell,sun,lll-tis}!sybase!ben