Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003

    Unanswered: dump tran at checkpoint

    I am working in an environment where we always have tran log issues.
    Also we back up our Database everyday. So in other words we really do not need tran log for recovery purposes.

    Having said the above,

    If I know a given SQL will take up a lot of tran log space, can I execute the following command after the SQL statements:

    dump tran spr_credit_batch with truncate_only checkpoint

    Would running the above command affect any other SQL statements being executed by someone else or some other script.


  2. #2
    Join Date
    Feb 2002
    Willy is on vacation
    why not setup log thresholds and that will automatically take care of the dump?

    Another approach is to write a proc that returns the log free space. You regualrly check the lo free space within your tran if it falls below a certain threshold, you rollback else commit

  3. #3
    Join Date
    Sep 2002
    Sydney, Australia

    Ma, the Green Elephant is Filling the Living Room


    1 You will have difficulties dealing with the symptoms (frequent tran log full), and they will never end, as long as you do not deal with the cause of the problem. If you want to deal with the cause, rather than the symptom, you will need to break up the massive transactions into manageable batches. Once there are windows between the batches, the tran log CAN be dumped by either of the above techniques, otherwise it cannot be dumped.

    2 Sybase provides full recovery, but you need the series of tran log dumps. However, if you dump the tran with truncate_only or no_log, you lose the series of tran log dumps, and you only have the db dump to rely on. You may find this link helpful in understanding.

    3 Set the db_option abort_tran_on_log_full, to separate the social from the anti-social jobs and allow the social jobs to continue, and the anti-social ones to fail.

    4 With a bit of design and management, eg. implement Willy's suggestion, you can have a recoverable db and a bit of peace. Wrestle with the massive "transactions", not the tran log.

    Last edited by DerekA; 08-29-06 at 02:01. Reason: Clarity
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts