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 http://www.dbforums.com/showthread.php?t=1604945 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.
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