Sql-server – How to delete large amount of data in sql server without data loss

deleteperformancequery-performancesql serversql-server-2012

I've been dealing with million of data deletion in day to day process.
Basically I have 4 tables.

Table_A
Table_B
Table_C
Table_D

I'm attempting to delete data older than 10 days in all tables.

Possibly I will be deleting around a million in each table.
I have created a stored procedure to do these operations.

The steps I have followed to delete the data is

Step 1: Move the recent days (data which I have to preserve) to a temp table

select * into Table_A_Temp
from Table_A
where <<where clause last 10 days to till date>>

Step 2: Rename main table to old table (table with all days data)

exec sp_rename 'Table_A', 'Table_A_Old'

Step 3: Rename temp table to main table (table with data between last days to till date)

exec sp_rename 'Table_A_temp', 'Table_A'

Step 4: Query the temp table with time frame if any new data is inserted during the copy process

Insert into Table_A
select * from Table_A_old

Step 5: Drop old tables

DROP TABLE Table_A_old

Step 6: Create keys and constraints in main table (means renamed table)

code to create primary keys and constraints

Problem:
If I continuously inserting data into table while the store procedure is running I'm losing data for few seconds. (all 4 tables)

Case 1: While renaming table

when I rename the main to old and temp table to main

I'm getting invalid object error (that table is exist error)

Case 2: Two of my tables have foreign key relation
If I insert data before creating constraints and key I'm getting related errors.

How to handle and delete the data properly without losing data.
Please advice the best practices.

Best Answer

Use batched deletes.

DECLARE @keepgoing bit = 1;

WHILE (@keepgoing = 1)
BEGIN
  DELETE d
  FROM
  (
  SELECT TOP 100 *
  FROM Table_A
  WHERE Created < DATEADD(DAY, -10, GETDATE())
  AND NOT EXISTS (....FK check...)
  ORDER BY Created
) d;

IF (@@ROWCOUNT = 0) SET @keepgoing = 0;
END
Related Question