Hi,

I have upgraded Sybase from 15.0.2 to 15.7 .When i perform insertion Continuously,my DB log space is filled and its giving me below error.

================================================== ===
(1 row affected)
Space available in the log segment has fallen critically low in database
'dbname'. All future modifications to this database will be aborted until the
log is successfully dumped and space becomes available.
(1 row affected)
Msg 1105, Level 17, State 4:
Server 'pqbsyb1', Line 13:
Can't allocate space for object 'syslogs' in database 'dbname' because
'logsegment' segment is full/has no free extents. If you ran out of space in
syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the
size of the segment.
================================================== ===
When i analysed how to overcome this i found 2 way below,

1. enable "trun log on check pt"

(or)

2.dump transaction (using stored procedure by continuously checking for the log free pages)

Is there any feature tat got added in sybase 15.7 ESD#01 that can be helpful in this situation other than i have mentioned above.

Thanks.
NAFIHA.

Hi,
This is not dependent on the version of ASE, its basically the way ASE log based recovery works.
Let me explain u a bit more.
In ASE database consist of three default segments
1. Default
2. System
3. Log.

For more information on segments please follow below link

what is segment in sybase?Know System segments system,log segment,default segments.

Now for your situation, the option you mentioned are fine to deal with.
Its totally depends, the option you use, on the how much the recovery of the database is important, in case of disaster.

1. If you use trunc log on chekpoint, option, all the committed transaction from the log will be removed from database logsegment (syslogs table), everytime ASE checkpoint runs.(which is default on every 60 sec)

2. If the database recovery is important up to time, in case of disaster , you must not use the trunc log on chkpt, and should go for dump tran.

the method of SP u told in you post is good, which seems to be a complex SP for checking the logsegment and deciding when to run dump tran. (You must be talking about the last chance threshold SP)
Whereas there is lot more simpler method and is used every where( I assume), which is schedule a job to run the transaction dump using a scheduler (cron in case of UNIX plateform) every 10 or 15 mins, and use your last chance threshold SP also, to take transaction dump if transaction log is crossing the threshold defined by you.

I hope my post will be helpful to you

Hi,

Your post was helpful. Thanks.

Some more clarifications.
1. Am "Dumping transaction" by setting various threshold levels of free pages.So when tat free pages reaches it dumps the transaction logs.Are you saying this as the complex procedure??

2.How last chance threshold differs from normal thresholds.Does "last chance threshold" helps us in anyway??

3.Are you asking me to schedule cron jobs so that it executes every time interval i mentioned (So there is no need of checking free pages!!) And We can easily clear log space.

Waiting for your confirmation on these!

And One more,

When i used the below 3 options for clearing log space,

1.dumping transaction manually.
2.dumping transaction by setting threshold levels.
3.Enable "trun log on chk point"

i was able to clear log space of the user database "db_name" i created using the above 3 options(which has log segment on separate device and data segment on different device)

But when i use the same 3 option for "tempdb" database,i found those 3 options were not helpful in clearing the logs.I found my tempdb log space remains same for long time even with the above options.This DB has both data and log segment on the same device.What could be the reason for this???

Your help is much appreciated!!

Thanks,
Nafiha

Hi,
I was glad that my post was helpful to you.
So lets start answering next set of questions.

1. Am "Dumping transaction" by setting various threshold levels of free pages.So when tat free pages reaches it dumps the transaction logs.Are you saying this as the complex procedure??
Ans: yes its complex to me as I am very bad in writing queries and sps

2.How last chance threshold differs from normal thresholds.Does "last chance threshold" helps us in anyway??

Ans: last chance threshold is the procedure that is used by ASE to clear the logsegment(most probably it runs lctadmin, I am not sure about this).
there is a sp sp_addthreshold to add the new threshold, it requires a sp also as an argument. in this SP u need to write the action that you want to execute as the threshold set by you is reached(in ur case it should be dumping the tran logs)

3.Are you asking me to schedule cron jobs so that it executes every time interval i mentioned (So there is no need of checking free pages!!) And We can easily clear log space.

Ans : yes you can write a cron job and schedule it to run on some regular interval, which suits to your case. Also as you had written the new threshold action procedure and added a new threshold, it will take care the tran dumps if log threshold reached between two fires of tran dumps.

For rest of the questions i will come to u latrer, need to leave now.

Yea..
Ok . Thanks. Don't forget to reply.


NAFIHA