Home » Databases » Sybase » ASE » How to break up a transaction without filling up the log

How to break up a transaction without filling up the log

Say you have a query that easily fills up the transaction log of the Sybase ASE 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:

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())[/sql] Break up the transaction based on the number of rows affected. [sql]declare @rows_affected int 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 commit tran else rollback tran end set rowcount 0[/sql]

Share Button

Leave a Reply

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

*
*

Facebook login by WP-FB-AutoConnect