Controlled delete, for large data sets, allows transaction log to clear
The syntax for this operation is different for ASE 15 ... this will be posted soon!
between batches
-- This example: 9000 rows in a batch, transaction log needs to clear after
200mb is allocated
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'
select @mb = ( data_pgs (8, doampg) ) * 8 / 1000
from sysindexes where id = 8
while @mb > 200
begin
waitfor delay "00:02:00"
select @mb = ( data_pgs (8, doampg) ) * 8 / 1000
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
|
|
