Thread: Extend segment on same device
07-16-04, 06:48 #1Registered User
Provided Answers: 4
- Join Date
- Jan 2004
Unanswered: Extend segment on same device
I have a database on 1 data- and 1 logdevice on a 126.96.36.199 HP Tru64 Unix-box.
The database is 1 GB, 600MB data, 400MB log. The datadevice has 400MB of space left, the logdevice 600MB.
A "logsegment full"-error appears in the errorlog, so I have to extend the logsegment (or so it says ).
It seems impossible to extend the logsegment on that same logdevice (or datadevice, I don't understand it).
Can anyone tell me how to solve this? I want to avoid all those extra devices for 1 database. I can resize the device if needed.
Thanks!I'm not crazy, I'm an aeroplane!
07-16-04, 11:00 #2Registered User
- Join Date
- Aug 2002
- Madrid, Spain
My advice is not to rush to increase the logsegment
unless you have decided that it became small for
daily use. Often, a uncommon event drove to a
full logsegment, and the solution is to react to that
First, query the last active transaction for the db.
db's log cannot be truncated further if that
transaction doesn't finish. The query is:
select * from master..syslogshold where dbid=...
Usually, that identifies a connection (by its spid)
falling in one of these three categories:
1 - Long running tran. Someone forgot to close it
or the program failed leaving the ASE connection
open. Just kill it and dump tran.
2 - Huge tran, probably inserting, updating or
deleting thousands of rows. If that process can
be broken down in several smaller transactions,
tell the programmer to do it. Meanwhile, kill the
huge connection, wait for its rollback (a long wait)
and dump tran.
3 - "Ghost connection": the spid doesn't appear in
master..sysprocesses. Usually, ASE's errorlog shows
a stack trace involving that spid. Since this kind of
connection cannot be killed, you'll have to reboot ASE.
Warning: startup will take a long time.
Other causes are also possible.