Sybase ASE data purge batch - waitfor and transaction log management

I am working on a Sybase ASE (migrating to 15.7) data purge utility to be used by multiple tables/ databases to delete huge amount of unwanted older data. I am planning to schedule it nightly 12:00 to 3:00 AM and after 3:00 AM, it is supposed to sleep and wake up at 12:00 PM next day and continue the purge. The criteria is not to affect the users.

Is this a good design to use the following to put the batch to sleep until the next day? or is it going to harm the performance? I am committing the tran before invoking waitfor.
```
waitfor time "12:00:00"
```

Transaction log:

I am finding the transaction log size using the below query and waiting untill the transaction log available space < some%. Is this a good approach?

Also, considering my generic scenario, what is the optimal transaction log used %limit that I should check for before going on wait? -Should I use "dump transaction"? I read that we shouldn't use "dump transaction truncate only" in production systems. Should I use it in a different way? Do you suggest me something for my scenario?

select @tlogPctUsed = ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) then u.size end)))
  from master..sysdatabases d, master..sysusages u
 where u.dbid = d.dbid
   and d.dbid = db_id()
   and d.status != 256
group by d.dbid

while (@tlogPctUsed > @tlogPctLimit)
begin
    waitfor delay @10Seconds
    select @tlogPctUsed = ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) then u.size end)))
      from master..sysdatabases d, master..sysusages u
     where u.dbid = d.dbid
       and d.dbid = db_id()
       and d.status != 256
    group by d.dbid
end

Solution

ASE Job Scheduler would be a more stable method of running your purge, instead of waitfor.

The reason *dump transaction with truncate_only* is not recommended for production systems is it's impact on recoverability. You can work around this issue by dumping the transaction logs to file as part of your process. That way you can deal with both the log filling issue, as well as maintaining the ability to recover your system in case of database failure.

More information on dumping the transaction log can be found in the Sybase ASE 15.7 Reference Manual: Commands

Also, sp_thresholdaction can be setup to automatically dump the transaction log when it reaches a certain threshold. The Sybase ASE 15.7 Reference Manual: Procedures has that example.