Application System Data Removal solutions

Demand:

As some of the company's old systems in the development of the line did not take into account the data need to delete the problem, causing the system to use a decade later the database is too large, need urgent real-time backup to delete data

1. Back up data to the backup repository

2. Delete data

3. Shrinking database Data files

Because of the need to minimize the impact on the system, it is generally done by following the steps

1. If the system does not need real-time query tables (such as the System Log table), create a new table instead, change the old table to Tablename_old and then perform the next steps

2. If the table has a self-increment column ID, it was deleted with the self-increment column ID as the lookup value

3. Delete a fixed number of rows at a time to avoid a long lock-up table (either table or row level, typically row-level locks)

4. Record deletion time and output for easy monitoring

The code is as follows

declare @st_num int = 1--start Position of the deletiondeclare @st_deletion int = 9999--row Numer of Eve Ry deletiondeclare @Sql1 varchar--backup data sqldeclare @Sql2 varchar--delete data sqldecla Re @start_time datetime--for caculating Delete timedeclare @end_time datetime--for caculating Delete Tim Edeclare @dur_time int--for caculating Delete timewhile (@st_num <=5000000) beginset @Sql1 = ' INSERT INTO Lin KServer.LinkDB.dbo.LogOldselect * from Log where logid between ' + convert (varchar), @st_num) + ' and ' + CONVERT (varch AR (+), @[email protected]_deletion) Set @Sql2 = ' Delete from Log where logid between ' + convert (varchar), @st_ num) + ' and ' + CONVERT (varchar (+), @[email protected]_deletion)/*execution*/set @start_time =getdate () print @ Sql1exec (@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 @Sql2exec (@Sql2) set @end_time =getdate () set @dur_time =datediff (SS, @start_time, @end_time) Print (' finished in ' + CONVERT (varchar, @dur_time) + ' seconds ') set @[email protected][email protected]_ Deletion+1end

　　

Application System Data Removal solutions