1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Large-scale delete in SQL Server 2000

Discussion in 'SQL Exams' started by Gaz 45, Feb 12, 2007.

  1. Gaz 45

    Gaz 45 Kilobyte Poster

    404
    4
    39
    Alright then lads & ladies, anyone have any tips on carrying out large scale deletes (millions of rows) without creating a transaction log bigger than Ayer's rock?

    Let's have a discussion! :)
     
    Certifications: MCP (70-229, 70-228), MBioch
    WIP: MCDBA (70-290)
  2. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,623
    115
    224
    What percentage of the total number of rows is that?

    If this is high consider doing a SELECT INTO a temp table with the oposite logic, truncate the original, and copy back.

    But don't be nasty to the transaction log. That's there so that you can recover from horrors. Treat it nice!

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  3. supag33k

    supag33k Kilobyte Poster

    461
    19
    49

    Yes a very good point about opposite log Harry!

    Remeber the difference between logged operations such as DELETE and non logged such as Truncate with regards to the transaction log.

    Note that Truncate takes out all rows!

    http://www.sql-server-performance.com/misc_tips.asp
     
    Certifications: MCSE (NT4/2000/2003/Messaging), MCDBA
    WIP: CCNA, MCTS SQL, Exchange & Security stuff
  4. zardoz

    zardoz Bit Poster

    19
    0
    14
    If you want to get rid of everything , truncate.

    Otherwise, do your deletes, then do a transaction log backup.
    Problem solved.
     
    Certifications: MCITP Sql Server 2005 (Developer)
    WIP: MCTS .Net
  5. Crito

    Crito Banned

    505
    14
    0
    Not to nit pick, but some people make a big deal about it. Truncate table is technically a minimally logged operation. It doesn't work on individual rows and it won't fire triggers, like a fully logged operation (delete), but it's not quite a non-logged operation either.
     
    Certifications: A few
    WIP: none
  6. JonnyMX

    JonnyMX Petabyte Poster

    5,239
    211
    236
    FORMAT C:
     
    Certifications: MCT, MCTS, i-Net+, CIW CI, Prince2, MSP, MCSD

Share This Page

Loading...