Need Tranaction Log space recommendation

[ps_vinay]

Hello,

One of our PROD Db Sized about 386505.0 MB in size(overall DB size data+log) and the transaction log size is 4200.0 MB which is roughly 1% of the overall DB size.

In this DB there will be lot of inserts/updates will occur. And also no options has been set for DB. Last week due to lot of inserts/updates log space got full and the DB got hung(we were not able to kill the SPIDS ) and we had to recycle the server and during recovery this Db did not recovered complaining enough log space during the undopass and we had to drop the Db and recreate it.

So, What are the recommendations for the transaction log space(how much to increae approx) and any recommmendations for the DB options in order to avoid the above situation.

Note : This DB is present in DR server also.

Thanks,

P S Vinay

[jayrijnbergen]

Which Sybase version are you running on?

ASE 15.7 SP100 and higher support "log off" for alter database. So worst case when you hit this issue you can increase the log with few G and remove it afterwards again.

Function loginfo(), also ASE 15.7, can give you better insight in what is using the available logspace

example from Sybooks:

select loginfo(dbid, 'database_has_active_transaction') as has_act_tran,

  loginfo(dbid, 'oldest_active_transaction_pct') as Act_log_portion_pct,

  loginfo(dbid, 'oldest_active_transaction_spid') as OA_tran_spid,

  loginfo(dbid, 'can_free_using_dump_tran') as dump_tran_free_pct,

  loginfo(dbid, 'is_stp_blocking_dump') as is_stp_blocking,

  loginfo(dbid, 'stp_span_pct') as stp_span_pct

[ps_vinay]

Thanks for the prompt response Bret.

The insert/update trans are big. long running transactions were also involved.

transaction log backup happens every for 10mins through crontab.

no db options are set currently.

-Vinay

[former_member89972]

Normally I would have "abort tran on log full"  option set on all databases.

This way it reduces some aggravation.

Also needed is monitoring of open and long running transaction visible from master..syslogshold.

This should tell you who is most probably causing the issue.

Most of the shops use combination of scheduled and threshold triggered transaction backups.

This way any abnormal high activity can be also be addressed.

10 minutes could be too long for a hungry update affecting millions of rows.

I have seen anywhere from 10% to 25% for log space.

But it will depend on your local situation/application profile etc.

Do NOT fall in the trap of increasing log space whenever users ask.  Instead ask them to break down the transaction in manageable batches/chunks and commit often.  Log space  -- just like tempdb --- is a shared resource and each connection needs to play by some acceptable rules !!

HTH

Avinash

[Former Member]

From your description, the dump tran frequency is high ( every 10 minutes through cronjob ),

I will suggest you to enlarge your log space to at least 3 times of current log space.

normally, 10~25% of data space should be consider to allocate for log space in the beginning,

and you really need to estimate appropriate log space for your production ASE by monitoring your daily activities (task type - OLTP, OLAP, batch ).

And you might need to consider to put your long running tasks to non-peak time to avoid impact to you OLTP tasks since you are not able to dump tran to release active part of log space.

HTH, Thanks.

Robert

[former_member188958]

It is hard to recommend an amount to increase the log by as the size needed depends on the amount and nature of the transactions being run on the database and how the database is set up to backup or at least truncate the log.  It could be that you have more than enough log space and just need to better manage keeping the log pruned.

Did the insert/update activity consist just of lots of quick transactions, or were big, long running transactions involved?   ASE can only prune the log to be beginning of the oldest open transaction, so if your application uses long-running transactions the log needs to be large enough to hold everything that is logged until the big transaction commits - and without knowing how big those transactions are, we can't suggest how big the log must be except to say "keep making it larger until you stop getting 1105 errors on the log segment".  If there are just many short transactions, the log can generally be truncated much more frequently.

What is your current backup strategy? i.e., do you take transaction log backups?

If so, are they just done on a regular schedule, or do you have thresholds configured to automatically dump the transaction log when it gets to certain levels of fullness?

If you aren't taking transaction log dumps, is the database set to truncate log on checkpoint?

-bret