Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Location
    Australia
    Posts
    8

    Unanswered: Non-logged delete of records???

    I have a database in Simple mode from which I wish to delete several million rows of one table (not all rows so 'TRUNCATE TABLE' is no good), without making the transaction log file grow to several GB in size. Any ideas??? I'm almost at the point where I might create a new table and copy the records I want to keep instead...
    Thanks, Simon.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Assuming the rows have a primary key and you can select the PK from the Primary table , you could setup a secondary table (PKToDelete) with, say, 10,000 PK , populate that table with a select, and then join the PKToDelete and Primary tables for the delete operation. Upon successful completion of the delete, truncate the PKToDelete table, and repeat at perodic intervals until all Primary table deletes are complete.

    Since you are in simple mode, the transaction log will be checkpointed when your pulsed delete completes, thereby preventing major growth of the transaction log.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    delete in small batches

    Code:
    declare @batchsize int
    set @batchsize=10000
    set rowcount @batchsize
    delete from bigtab where something='some value'
    while @@rowcount=@batchsize
    begin
      delete from bigtab where something='some value'
    end
    set rowcount 0
    Edit:
    PS. Can do the same for updates e.g.
    Code:
    declare @batchsize int
    set @batchsize=10000
    set rowcount @batchsize
    update bigtab set type='shrub' 
    where id between 2867127 and 1908917872
      and type='tree'
    while @@rowcount=@batchsize
    begin
      update bigtab set type='shrub' 
      where id between 2867127 and 1908917872
        and type='tree'
    end
    set rowcount 0
    Last edited by pdreyer; 11-03-06 at 04:20.

Posting Permissions

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