title: Sybase ASE data purge batch - waitfor and transaction log management
body: <p>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.</p>
  
  <ol>
  <li><p>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.</p>
  
  <pre><code>waitfor time "12:00:00"
  </code></pre></li>
  <li><p>Transaction log:</p>
  
  <ul>
  <li>I am finding the transaction log size using the below query and waiting untill the transaction log available space &lt; some%. Is this a good approach?</li>
  <li><p>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?</p>
  
  <pre><code>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 &gt; @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
  </code></pre></li>
  </ul></li>
  </ol>