应用系统数据删除解决方案

需求:

由于公司一些旧系统在开发上线时没有考虑到数据需要备份删除的问题, 导致系统使用了十年之后数据库过大, 需要进行紧急的实时备份删除数据

1. 备份数据到备份库

2. 删除数据

3. 收缩数据库数据文件

 

因为需要尽量减少对系统的影响, 一般会按一下步骤操作

1. 如为系统不需要实时查询的表(如系统日志表), 则创建新表代替, 把旧表改成TableName_Old然后再执行后续步骤

2. 如表有自增列ID, 曾以自增列ID为查找值进行删除

3. 每次删除固定行数, 以免引起长时间锁表(无论表级或行级, 一般为行级锁)

4. 记录删除时间并输出方便监控

 

代码如下

declare @st_num int = 1             --Start Position of the Deletion
declare @st_deletion int = 9999     --Row Numer of Every Deletion
declare @Sql1 varchar(5000)         --Backup Data SQL
declare @Sql2 varchar(5000)         --Delete Data SQL
declare @start_time datetime        --For Caculating delete time
declare @end_time datetime          --For Caculating delete time
declare @dur_time int               --For Caculating delete time

while(@st_num<=5000000)

begin
set @Sql1='
insert into LinkServer.LinkDB.dbo.LogOld
select *
  from Log where LogID between '+ convert(varchar(100),@st_num) + ' and ' + convert(varchar(100),@st_num+@st_deletion)

set @Sql2='
delete
  from Log where LogID between '+ convert(varchar(100),@st_num) + ' and ' + convert(varchar(100),@st_num+@st_deletion)

/*
Execution
*/
set @start_time=GETDATE()
print @Sql1
exec (@Sql1)
set @end_time=GETDATE()
set @dur_time=DATEDIFF(ss,@start_time,@end_time)
print('Finished in ' + convert(varchar,@dur_time) + ' seconds')

set @start_time=GETDATE()
print @Sql2
exec (@Sql2)
set @end_time=GETDATE()
set @dur_time=DATEDIFF(ss,@start_time,@end_time)
print('Finished in ' + convert(varchar,@dur_time) + ' seconds')

set @st_num=@st_num+@st_deletion+1

end