Skip to Content

Need Tranaction Log space recommendation

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Dec 02, 2015 at 04:18 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 02, 2015 at 04:53 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Dec 09, 2015 at 09:34 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.