Sql Server: chunking deletes still fills up transaction log; on fail all deletes are rolled back - why?

Question

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.

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 MyLogTable, TRUE 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.)

I then try to chunk deletes (say, 5000 at a time) by looping a call to a sproc that basically does this:

DELETE TOP (@RowsToDelete) FROM [dbo].[MyLogTable]  

until I've deleted what needs to be deleted.

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

dbcc sqlperf (logspace)  

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.

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.

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?

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.

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

checkpoint
dbcc dropcleanbuffers

it drops right back down to about 5 percent utilization.

TIA.

Answer 1

The log file in simple recovery model is truncated automatically every checkpoint gererally speaking. You can invoke checkpoint manually as you do at the end of the loop, but you can also do it every iteration. The frequency of checkpoints is by default determined automatically by sql server based on the recovery interval setting.

As far as the 'all deletes are rolled back', I don't see other explanation but an external transaction. Can you post entire code that cleans up the log? How do you invoke this code? What is your setting of implicit transactions?

Hm.. if the log grows and doesn't truncate automatically, it may also indicate that there is a transaction running outside of the loop. Can you select @@trancount before your loop and perhaps with each iteration to find out what's going on?

Answer 2

Well, I tried several things, but still all deletes get rolled back. I added printint @@TRANCOUNT both before and after the delete and I get zero as the count. Yet, on failure, all deletes are rolled back .... I added SET IMPLICIT_TRANSACTIONS OFF in several places (including within my initial call from Query Analyzer, but that does not seem to help. This is the body of the stored procedure that is being called (I have set @RowsToDelete to 5000 and 8000):

  SET NOCOUNT ON;
  print N'@@TRANCOUNT PRIOR TO DELETE: ' + CAST(@@TRANCOUNT AS VARCHAR(20));

  set implicit_transactions off;
  WITH RemoveRows AS 
  (
    SELECT ROW_NUMBER() OVER(ORDER BY [Date] ASC) AS RowNum
    FROM [dbo].[Log4Net]
  )

  DELETE FROM RemoveRows
  WHERE RowNum < @RowsToDelete + 1

  print N'@@TRANCOUNT AFTER DELETE: ' + CAST(@@TRANCOUNT AS VARCHAR(20));

It is called from this t-sql:

WHILE @RowsDeleted < @RowsToDelete
BEGIN
 EXEC [dbo].[DeleteFromLog4Net] @RowsToDelete 
 SET @RowsDeleted = @RowsDeleted + @RowsToDelete
 Set @loops = @loops + 1
 print 'Loop: ' + cast(@loops as varchar(10))
END

I have to admit I am puzzled. I am not a DB guru, but I thought I understood enough to figure this out....