Controlled delete, for large data sets, allows transaction log to clear.
The syntax for this operation is different for ASE 15 ... be sure to comment
out the 12.X code if you are using ASE 15.X
--This example: 9000 rows in a batch, transaction log
--needs to clear after 200mb is allocated between batches
select @@servername, db_name()
go
set nocount on
go
set rowcount 9000
go
declare @rows integer,
@trows integer,
@mb money
select @rows=1,@trows=0
while @rows > 0
begin
delete from invoice_history
select @rows = @@rowcount
select @trows = @trows + @rows
select @trows, 'tLog=', @mb
waitfor delay '00:00:02'
-- ASE 12.X
select @mb1 = ( data_pgs (8, doampg) ) * (@@maxpagesize/1000.0) / 1000
from sysindexes where id = 8
-- ASE 15.X
select @mb1 = ( ( data_pages (db_id(), 8, doampg) ) * (@@maxpagesize/1000.0) / 1000.0)
from sysindexes where id = 8
while @mb > 200
begin
waitfor delay "00:02:00"
-- ASE 12.X
select @mb1 = ( data_pgs (8, doampg) ) * (@@maxpagesize/1000.0) / 1000
from sysindexes where id = 8
-- ASE 15.X
select @mb1 = ( ( data_pages (db_id(), 8, doampg) ) * (@@maxpagesize/1000.0) / 1000.0)
from sysindexes where id = 8
select @mb "trans log MB"
end
end
go
select 'Table is now empty:', 'invoice_history', count(1) from invoice_history
|
|
