There must be a way to delete data in SQL Server w/o overloading the log

Question

I need to delete a bunch of data, and don't have the disk space for the log to continue growing. Upon looking into the matter further, it looks like there isn't any way around this, but I thought I'd ask for sure; it's difficult for me to believe that something so simple is impossible.

I tried looping, deleting in chunks, and calling shrinkfile on the log after each iteration. SQL Server just seems to ignore the shrinkfile command. Did the same with backup log (then deleting the backup file afterwards). Same thing - log just keeps on growing. The recovery model on the database I'm trying this on is simple - I thought that would make it easier, but it doesn't.

Answer 1

Do the delete in chunks, but rather than trying to shrink the log between times, do log backups between the chunks (that is if you're in full recovery)

The problem is that the log is full and hence has to grow. If it's full, trying to shrink it is useless, there's no free space in the log to release to the OS. What you need to do instead is make the space inside the file available for reuse.

Since the DB is in simple recovery, run the delete in chunks with a CHECKPOINT command in between each chunk. You can't do log backups in Simple recovery

Here's sample code that does deletes without filling the log (in simple recovery). DO NOT wrap this in a custom transaction. That completely defeats the point of deleting in batches as the log can't be cleared until the entire transaction commits.

(SQL 2005 and above. For SQL 2000, remove TOP and use SET ROWCOUNT)

DECLARE @Done BIT
SET @Done = 0
WHILE @Done = 0
  BEGIN
    DELETE TOP (20000) -- reduce if log still growing
      FROM SomeTable WHERE SomeColumn = SomeValue 
    IF @@ROWCOUNT = 0 
      SET @Done = 1
    CHECKPOINT -- marks log space reusable in simple recovery
  END

To understand log management, take a look at this article - http://www.sqlservercentral.com/articles/64582/