Tom_Hickerson
asked on
How do I Delete millions of data points and Store the deleted records in SQL 2005?
Hi guys,
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:
USE [master]
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
BEGIN
DELETE top (100000)
FROM dbo.table1
WHERE Datetime<= DATEADD(d, -14, Getdate())
IF @rows_deleted=0
BEGIN
CHECKPOINT
END --IF
WAITFOR DELAY '00:00:00.15'
END --WHILE
PRINT 'Total Rows Deleted=' + CAST (@total_rows_deleted AS varchar (10))
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:
USE [master]
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
BEGIN
DELETE top (100000)
FROM dbo.table1
WHERE Datetime<= DATEADD(d, -14, Getdate())
IF @rows_deleted=0
BEGIN
CHECKPOINT
END --IF
WAITFOR DELAY '00:00:00.15'
END --WHILE
PRINT 'Total Rows Deleted=' + CAST (@total_rows_deleted AS varchar (10))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The row count was left over from a query I previously used on another set of tables and was not intended for this query.
http://msdn.microsoft.com/en-us/library/ms177564.aspx