Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    72

    Unanswered: Delete operation on a big table

    Hi,

    I have a table with 50 million records and have to delete 10 million records based on one condition. what is the best way to do this (without using much resources and also less time).

    can we split those 10 million records and delete few at a time (say one million at a time).

    Let me your ideas on this?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    declare @rowcnt int, @batchsize int
    select @batchsize=1000000 -- Delete in batches of this amount
      ,@rowcnt=@batchsize
    set rowcount @batchsize
    while @rowcnt=@batchsize
    begin 
      delete records where thedate<'20100101'
      set @rowcnt=@@rowcount
    end

  3. #3
    Join Date
    Jan 2010
    Posts
    72
    Thanks for your reply.
    Seems I confused. I would like to know what is the best way to delete.

  4. #4
    Join Date
    Jun 2010
    Posts
    51
    Assuming original table is table_1

    How about:
    1. Inserting the rest 40 million into a different table (use select * into).
    2. Truncate and than drop the old table (table_1)
    3. rename the table to table_1 created at step1
    4. run sp_recompile table_1

    Log usage will be minimal (in-fact if you do it rite, there will be no log usage) and operation will be much faster.
    Please always reply to the post if it was helpful. Others may find it helpful.

  5. #5
    Join Date
    Jan 2011
    Posts
    3

    Delete operation on a big table

    But truncate table is non log operation. If you want to recover the data from transaction log then it will not be possible.It will stop your dump transaction,if any scheduled. And you have to make dump database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •