Deleting thousands of rows in a table

• mipo

  Ten Centuries

  Points: 1346

  More actions

  April 10, 2003 at 2:40 pm

  #159479

  How can I quickly delete thousands of rows in a table (SQL2000) according a query and without blowing up the log file? For instance executing the query:

  Delete from transactions

  WHERE transactiondatestamp < DATEADD (m,-4,GETDATE())

  increases my log file to almost 6GB before job was done an normal size was re-obtained. In addition it took a long to time to get the job done.

  With the command truncate table I cannot use query unfortunately but this would be faster.

  Anyone has an idea?

  mipo

• SJTerrill

  SSCrazy

  Points: 2938

  More actions

  April 10, 2003 at 2:50 pm

  #453792

  mipo, Could you do the deletes in several, smaller batches?

  
  
  WHERE DateDiff(mm,transactiondatestamp,GetDate()) = 4
  
  .
  
  .
  
  WHERE DateDiff(mm,transactiondatestamp,GetDate()) = 3
  
  

  Or, if it's particularly large, maybe do DateDiff(dd,transactiondatestamp,GetDate()) = 90, then 89.

  Regards,

  SJTerrill