Home » Databases » Sybase » ASE » Sybase ASE / MS SQL Server: Preventing a transaction from filling up the transaction log

Sybase ASE / MS SQL Server: Preventing a transaction from filling up the transaction log

Say you have a query that easily fills up the transaction log of the Sybase ASE server / Microsoft SQL Server:

delete from rep_queues_archive
where sample_date < dateadd(wk, -1, getdate())

How would you break up the transaction so it doesn’t fill up the log? There are several ways to do it, but I’ll just cover two of them:

  1. Break up the transaction based on the actual data.

    delete from rep_queues_archive 
    where sample_date < dateadd(wk, -52, getdate()
    delete from rep_queues_archive 
    where sample_date < dateadd(wk, -51, getdate())
  2. Break up the transaction based on the number of rows affected.
  3. declare @rows_affected int
    declare @dbName varchar(50)
    select @dbName = db_name()
    set rowcount 1000
    
    select @rows_affected = 1
    
    while @rows_affected > 0
      begin
         delete from rep_queues_archive where sample_date < dateadd(wk, -1, getdate())
         select @rows_affected = @@rowcount
     
         if @@error = 0
          begin
            commit tran
            dump tran @dbName with truncate only
          end
         else
          rollback tran
         end
    
    set rowcount 0

    Of course, if you are performing incremental backups, you will want to modify the dump tran @dbName with truncate only line to read something like dump tran @dbName to “/sybdumps/mydb_tran.dmp”. Modify to suit your naming standard.

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect