title: Sql Server: chunking deletes still fills up transaction log; on fail all deletes are rolled back - why?
body: <p>Here is my scenario: we have a database, let's call it Logging, with a table that holds records from Log4Net (via MSMQ). The db's recovery mode is set to Simple: we don't care about the transaction logs -- they can roll over.  </p>
  
  <p>We have a job that uses data from sp_spaceused to determine if we've met a certain size threshold. If the threshold is exceeded, we determine how many rows need to be deleted to bring the size down to x percent of that threshold. (As an aside, I'm using exec sp_spaceused <code>MyLogTable</code>, <code>TRUE</code> to get the number of rows and a rough approximation of their average size, although I'm not convinced that's the best way to go about it. But that's a different issue.)  </p>
  
  <p>I then try to chunk deletes (say, 5000 at a time) by looping a call to a sproc that basically does this: </p>
  
  <pre><code>DELETE TOP (@RowsToDelete) FROM [dbo].[MyLogTable]  
  </code></pre>
  
  <p>until I've deleted what needs to be deleted.</p>
  
  <p>Here's the issue: If I have a lot of rows to delete, the transaction log file fills up. I can watch it grow by running </p>
  
  <pre><code>dbcc sqlperf (logspace)  
  </code></pre>
  
  <p>What puzzles me is that, when the job fails, ALL deleted rows get rolled back. In other words, it appears all the chunks are getting wrapped (somehow) in an implicit transaction.</p>
  
  <p>I've tried expressly setting implicit transactions off, wrapping each DELETE statement in a BEGIN and COMMIT TRAN, but to no avail: either all deleted chunks succeed, or none at all.</p>
  
  <p>I know the simple answer is, Make your log file big enough to handle the largest possible number of records you'd ever delete, but still, why is this being treated as a single transaction?</p>
  
  <p>Sorry if I missed something easy, but I've looked at a lot of posts regarding log file growth, recovery modes, etc., and I can't figure this out.</p>
  
  <p>One other thing: Once the job has failed, the log file stays up at around 95 - 100 percent full for a while before it drops back. However, if I run </p>
  
  <pre><code>checkpoint
  dbcc dropcleanbuffers
  </code></pre>
  
  <p>it drops right back down to about 5 percent utilization.</p>
  
  <p>TIA.</p>