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


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.

  • Can you post the error messages you're getting please? Commented Apr 25, 2019 at 7:57

4 Answers 4


Use batched deletes.

DECLARE @keepgoing bit = 1;

WHILE (@keepgoing = 1)
  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;

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):

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


INSERT INTO #deleteEntries ( Id )
SELECT deleteAlias.Id
FROM dbo.tableToDeleteFrom deleteAlias WITH (NOLOCK)

    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

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:

  • Large-scale deletion in batches can be tricky.
    – vonPryz
    Commented Apr 25, 2019 at 9:01
  • @vonPryz yes, it depends all on your purpose. If you want to delete in the fastest way, do it in a single transaction and cope with the size of the transaction log and locking. If you want to avoid locking and/or a small transaction log, then fall back to batches. Difficult to have both without going for other solutions like i suggested here: partitioning.
    – Azrael
    Commented Apr 25, 2019 at 9:09
  • @Azrael but locking can cause a slow-down of the deletion itself, so it isn't always "fastest" Commented Apr 25, 2019 at 9:19
  • @vonPryz - But all the ones he’s wanting to delete should be at one end of an index. So it should be easy enough.
    – Rob Farley
    Commented Apr 25, 2019 at 9:55
  • @RobFarley AN index - what if there are multiple indexes with different columns? Blocking those could slow things down too Commented Apr 25, 2019 at 9:57

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.


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()
    WHILE (@TopSize <= @MaxLimit)

        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
            SET @TopSize = @TopSize + @BatchSize


    --catch error

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.