Post reply

DELETE data in chunks

  • January 18, 2017 at 3:07 pm

    #401416

    I have data since 2015 , i want to keep only 14 days of data and delete the rest but in chunks.

    example :
    2015-1-1 is min date and max is 2017-1-18.

    Need data from 2017-1-04-till today. 
    <

  • January 18, 2017 at 3:17 pm

    #1923561

    Just use the TOP option in your DELETE statement.


    DECLARE @page_size INT = 50000; 

    WHILE @@ROWCOUNT > 0
    BEGIN
        DELETE TOP (@page_size) YourTableNameHere
        WHERE DateField < '2017-01-04'
    END 

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 18, 2017 at 3:36 pm

    #1923568

    Your ideal batch size will depend on the number of rows being deleted, but for a medium powered server I generally use a batch size about 10% size of total rows to be deleted, and if this is > 5 million rows, then drop it down to 5 million. So, when deleting 100 million rows, set batch size to 5 million.

    Also, to minimize transaction logging, you can disable (drop) all non-clustered indexes other than the one covering your WHERE clause predicate, which in your specific case would be the date column, and then re-enable (create) the indexes again afterward. If you're deleting a significant percentage of rows, any indexes enabled during the process will be fragmented afterward anyhow, so you might as well disable any you don't need while performing bulk delete operation.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • January 18, 2017 at 5:38 pm

    #1923583

    Frequently, a better solution (faster, less log space, smaller backups, etc, etc) is to copy the data you want to keep to a new table, rename the old table (so you can keep it for a while until you're sure), rename the new table as the original name, and eventually drop the old table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply