Post reply

Truncating Large Tables Problem

  • January 9, 2010 at 11:24 pm

    #218108

    I have a table that contains more than 10,000,000,000 records, what is the best way to empty this table, as i want the fastest way?

    I believe that using delete statements is not suitable for such operations because it will log the whole transactions.

    I'm trying to use the truncate command, but the statement has been working for the last 16 hours without any results yet.

    So did any way face the same problem before?

  • January 10, 2010 at 2:39 am

    #1101474

    Truncate Table is the fastest. It shouldn't take that long, sure it's not blocked by other transactions. Check sys.dm_exec_requests

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 12, 2010 at 11:47 am

    #1102640

    Bet you are blocked!


    * Noel

  • January 13, 2010 at 6:31 pm

    #1103387

    Here's what you can do.

    1. Create a new table with the same structure and indexes as the old table.

    2. Rename the old table with a different name

    3. Rename the new table to the old table name.

    Of course you want to do this during off-peak hours, because the users might face some interruption while the rename is happening although it should be pretty quick. Once this is done you can do a truncate on this old table or might even drop it if you really don't care about the old data.

    Thanks,

    Amol Naik

  • January 14, 2010 at 4:23 am

    #1103509

    Thank you all for your reply, i think you're right, and the problem is probably during to blocking issues.

    I'm working on this, and i'll update about the solution if the problem was solved.

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

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