How do I Delete millions of data points and Store the deleted records in SQL 2005?
Posted on 2015-01-05
I am trying to delete approximately 13.5 million data points while simultaneously moving the deleted data to a storage folder. When we try to look up production trends for the past two weeks the graph takes too long to load the 13.5 million points. The objective is to keep only the current data for the past fourteen days in the original table while storing the rest in old records. I have made a query to delete up to fourteen days ago by removing 100,000 rows at a time and would like to stick to a similar number because it allows me to see the progress of the query and does not overload the query.
Here is part of the table I am using:
id source_id datetime data
11720123 27 9/29/2014 12:33:31 PM 422
11720124 17 9/29/2014 12:33:31 PM 1969
11720125 25 9/29/2014 12:33:31 PM 771
11720126 16 9/29/2014 12:33:31 PM 518
11720127 6 9/29/2014 12:33:31 PM 1079
11720128 12 9/29/2014 12:33:31 PM 854
Here is the query I have used to delete the data thus far:
DECLARE @total_rows_in_table int
DECLARE @rows_deleted int
DECLARE @total_rows_deleted int
SELECT @total_rows_in_table = COUNT(*)
FROM dbo.table1 WITH (NOLOCK)
SET @rows_deleted = 0
SET @total_rows_deleted = 0
WHILE 1 = 1
DELETE top (100000)
WHERE Datetime<= DATEADD(d, -14, Getdate())
WAITFOR DELAY '00:00:00.15'
PRINT 'Total Rows Deleted=' + CAST (@total_rows_deleted AS varchar (10))