title: How can I reduce transaction log usage when deleting from a massive table in SQL Server?
body: <p>I had this function that deleted historical data that was no longer needed from a big table with over 10 million rows</p>
  
  <pre><code>DELETE FROM BigTable
  WHERE DATEDIFF(month,dtmtimestamp, getdate()) &gt; 2)
  </code></pre>
  
  <p>I then tried this method to reduce the amount of space the transaction log used:</p>
  
  <pre><code>WHILE (SELECT COUNT(*) FROM BigTable WHERE DATEDIFF(month,dtmtimestamp, getdate()) &gt; 2) &gt; 0 BEGIN
      DELETE TOP 10000 FROM BigTable
      FROM BigTable
      WHERE DateDiff(month,dtmtimestamp, getdate()) &gt; 2
  CONTINUE END 
  </code></pre>
  
  <p>Is this the right method? Or am I going to use up more transaction log this way?</p>
  
  <p>Any tips of good methods for this?</p>
  
  <p>Cheers,<br>
  Niko</p>
  
  <p><strong>Final Answer</strong>:</p>
  
  <pre><code>DECLARE @Remainder INT
  DECLARE @ChunkSize INT
  SET @Remainder = (SELECT COUNT(id) FROM BigTable WHERE dtmtimestamp &lt; DateAdd(month, -2, getdate()))
  SET @ChunkSize = CEILING(@Remainder/100) /* Divide the total into 100 parts, whole integers only */
  WHILE @Remainder BEGIN
      BEGIN TRANSACTION deletehistorical
      DELETE TOP (@ChunkSize)
      FROM BigTable
      WHERE dtmtimestamp &lt; DateAdd(month, -2, getdate());
      SET @Remainder = @@ROWCOUNT;
      COMMIT TRANSACTION deletehistorical
  END
  </code></pre>
  
  <p>The CHECKPOINT command only tell the engine to remove finished transactions from the log (in simple recovery mode) and as this query actually still keeps going each loop the transaction is still being created. So to break up the transaction I added a BEGIN and COMMIT to force the database to take those changes each time.</p>