create procedure sp_delete_records
@ld_day numeric(10) = -30
as
declare
@max_time datetime
set rowcount 1000
select @max_time = dateadd(dd,@ld_day,convert(datetime,convert(varchar,getdate(),111)))
select 1
while(@@rowcount>0)
begin
dump tran YOUR_DATABASE with truncate_only
delete from YOUR_TABLE where lasttime < @max_time
end
set rowcount 0
return
以上为存储过程
原理:使用循环,每次删除最多1000条记录,然后清除log,直到所有记录都删除