How to delete large amount of data in sql server without data loss?

Question

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.

Answer 1

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

Answer 2

As long as you perform table renames you won't be able to accomplish the task without some downtime unless you change the inserting process. If you can adjust the inserting process to perform a retry upon a failure you can overcome this shortcoming.

Another option would be to omit the table renaming and do all the actions within one table. You probably already realized that deleting a huge amount of data within the same table can be too slow and resolved to the table-switching strategy.

The best deletion strategy for a single table with constant inserts i found was this in a stored procedure (the batch size can be adjusted to your environment):

DECLARE @MONTHCOUNT int
SET @MONTHCOUNT = 24 -- delete records older than 24 months

CREATE TABLE #deleteEntries (Id INT NOT NULL PRIMARY KEY);

INSERT INTO #deleteEntries ( Id )
SELECT deleteAlias.Id
FROM dbo.tableToDeleteFrom deleteAlias WITH (NOLOCK)
WHERE deleteAlias.SendDate < DATEADD(MONTH, -@MONTHCOUNT, GETDATE())

WHILE 1=1
BEGIN
    DELETE dbo.tableToDeleteFrom
    WHERE Id IN (
        SELECT TOP 10 d.Id
        FROM #deleteEntries d
        ORDER BY d.Id
    )

    DELETE #deleteEntries
    WHERE Id IN (
        SELECT TOP 10 d.Id
        FROM #deleteEntries d
        ORDER BY d.Id
    )

    IF @@rowcount<10
        BREAK
END

This approach works up to a reasonable amount but after this you can't delete fast enough to keep up with the inserts.

For managing to delete an even bigger amount of data I suggest to go for table partitioning. This works very well for me. Here you partition your table e.g. into daily partitions. Like this you have your data already grouped for every day. As soon as you want to delete the data of a specific day as it is now older than 10 days you just have to delete this partition. Deleting a partition is done by moving the desired partition into a spare-table and then truncating the spare-table.

This task is an instant action and causes no downtime, no matter how much data/rows you want to delete.

Here some links to start with table partitioning:

• https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/
• https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/

Answer 3

It seems you are following very same approach that we follow in our organization i.e. perform insert instead of delete. And once data is validated from inserted table(new table), rename underlying table to something else i.e. arch and new table to main table.

You may refer my answers in below question:

Archiving process not running fast enough

I think in order to perform above operation without any data loss and make it consistent, you would need a small windows of down-time, that is how we are doing it. In our case, we take down time of 1.5-2 hours(based on volume of data) once in a year and we ensure that no new transaction comes in while we are executing archival.

In case, down time is not working out for you, you may target off-peak hour i.e. late night over weekend or public holidays.

For invalid object error, you need to ensure that table doesn't exist and possibly, you can store them in a different database on the same server to avoid any naming issue.

As far as foreign key is concerned, you need to really plan it well before starting the operation, start from the leaf table where foreign key is referred and touch base table in the next round. You also need to be considerate about filtered index.

I hope above helps.

Answer 4

What you tried is one of best approach ,but unfortunately in your case it didn't work.

I will create a Job,which will run at Down Time.

Also Batch Delete,and use or NOT use Transaction as per your requirement.

DECLARE @TopSize INT = 10000
DECLARE @BatchSize INT = 10000
DECLARE @MaxLimit INT = 1
DECLARE @RowCount INT = 0
-- clause last 10 days to till date or whtever your logic
declare @Date Datetime=dateadd(day,-10,getdate()
BEGIN TRY
    WHILE (@TopSize <= @MaxLimit)
    BEGIN

        BEGIN TRAN

    delete TOP ((@TopSize) from table_A
    where datecolumn<=@Date

   delete TOP ((@TopSize) from table_B
    where datecolumn<=@Date

    delete TOP ((@TopSize) from table_C
    where datecolumn<=@Date

    delete TOP ((@TopSize) from table_D
    where datecolumn<=@Date

        SET @RowCount = @@RowCount

        --PRINT @TopSize
        IF (
                @RowCount = 0
                OR @RowCount IS NULL
                )
            BREAK;
        ELSE
            SET @TopSize = @TopSize + @BatchSize

            COMMIT;
    END
END TRY

BEGIN CATCH
if(@@rowCount>0)
ROLLBACK
    --catch error
END CATCH