Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Posts
    93

    Unanswered: Delete Data without impacting Tempdb

    This is a SQL 2005 production server.

    I have to delete around 51 million rows from a table which has 149 million rows.

    Can't use truncate option as the other rows in the table are still needed.

    How can I delete the rows without filling up the tempdb ?

    If the tempdb fills up I can't bounce the server.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Set your recovery mode to Simple so you don't log all the deletes. Consider deleting your data in smaller batches.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I've been doing this exact thing for the past week, and did exactly that.

    Just remember to defrag/rebuild indexes following this.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    tempdb? maybe you need to check whether you are trying to support snapshot isolation. In that case, the deleted rows will end up in tempdb prior to the commit.

  5. #5
    Join Date
    Oct 2003
    Posts
    93
    The recovery mode is "simple". Its still putting all the transaction in the log file.

    The Tempdb detail was an error from my side.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    in that case, simply break the delete down into smaller chunks. The entire delete will always be logged. So instead of one big delete, make it 10 smaller deletes.

  7. #7
    Join Date
    Oct 2003
    Posts
    93
    thanks for all the responses.
    The only solution seems to delete in small batches and then truncate the log.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should not need to truncate the log in simple recovery mode.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Somehow it seems that some of the transaction log keeps getting held after large transactions. I find running checkpoint in the affected database brings things back in line. Checkpoint is also a side effect of truncating the log.

  10. #10
    Join Date
    Oct 2003
    Posts
    93
    Mcrowley,
    That is exactly the issue I have. I have to truncate the log after doing the large delete inspite of recovery mode simple.

  11. #11
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    After your done truncate with no_log and dbcc shrinkfile it back down.

  12. #12
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Bear in mind if you are performing this delete operation during normal day time it will have stress on tempdb too in addition to user database's transaction log.

    So better to perform this operation during less traffic hours and also perform CHECKPOINT after couple of thousand rows to keep up the log size.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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