locked
What is the Best way to delete huge number of records RRS feed

  • Question

  • User-273049309 posted

    I am using inline delete queries to delete records from LOG tables. These tables are filled by Triggers for any insert/update/delete due to which these tables have huge number of records. My query is like delete from table_name where log_timestamp < 3 years old date. My process need to at least handle 10 million records deletion. What would be best way to delete in chunks without any timeouts?

    Friday, April 29, 2016 3:49 PM

Answers

  • User77042963 posted

    You can delete your table in small batches. Here is a sample code:

    declare @Chunk int=500, @ToBeDeleted int = 1
      
    WHILE @ToBeDeleted > 0
      BEGIN
          BEGIN TRAN
     
          DELETE TOP (@Chunk) FROM [dbo].[accountlog]
          WHERE  createdate < Dateadd(day, -180, Getdate())
     
          SET @ToBeDeleted = @@ROWCOUNT
     
          --PRINT 'Deleted rows: ' + Cast(@ToBeDeleted AS VARCHAR(10))
     
          COMMIT TRAN
      END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 29, 2016 3:59 PM
  • User753101303 posted

    Hi,

    See perhaps https://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx solution 2 to delete a fixed count of rows each time and still keep a chronological order.. You'll have just to repeat that until DELETE doesn't delete anything.

    Edit: if you define that as a job it will just progressively "catch up" and then you'll be able to remain at 3 years of data.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 29, 2016 4:24 PM
  • User-1636183269 posted

    Praveen,

    Please use below query

    DECLARE @DeletedRecords INT;
    SET @DeletedRecords = 1;


    WHILE (@DeletedRecords > 0)
      BEGIN
         DELETE TOP (10000)  TableName
         WHERE entereddate< dateadd(MONTH,-36,GETDATE())
         SET @DeletedRecords = @@ROWCOUNT;
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 29, 2016 6:10 PM

All replies

  • User77042963 posted

    You can delete your table in small batches. Here is a sample code:

    declare @Chunk int=500, @ToBeDeleted int = 1
      
    WHILE @ToBeDeleted > 0
      BEGIN
          BEGIN TRAN
     
          DELETE TOP (@Chunk) FROM [dbo].[accountlog]
          WHERE  createdate < Dateadd(day, -180, Getdate())
     
          SET @ToBeDeleted = @@ROWCOUNT
     
          --PRINT 'Deleted rows: ' + Cast(@ToBeDeleted AS VARCHAR(10))
     
          COMMIT TRAN
      END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 29, 2016 3:59 PM
  • User753101303 posted

    Hi,

    See perhaps https://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx solution 2 to delete a fixed count of rows each time and still keep a chronological order.. You'll have just to repeat that until DELETE doesn't delete anything.

    Edit: if you define that as a job it will just progressively "catch up" and then you'll be able to remain at 3 years of data.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 29, 2016 4:24 PM
  • User-1636183269 posted

    Praveen,

    Please use below query

    DECLARE @DeletedRecords INT;
    SET @DeletedRecords = 1;


    WHILE (@DeletedRecords > 0)
      BEGIN
         DELETE TOP (10000)  TableName
         WHERE entereddate< dateadd(MONTH,-36,GETDATE())
         SET @DeletedRecords = @@ROWCOUNT;
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 29, 2016 6:10 PM