Shrink logs in between user transaction in SQL Server I have a database where there are like billions of rows and I need to delete the data based on some condition.

Initially I was trying to delete the code in one transaction and log starts growing to the hard disk and fails, then I tried to do it in chunks.

SET NOCOUNT ON;

DECLARE @r INT;

SET @r = 1;

WHILE @r &gt; 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (2000000) -- this will change
    Databasename.dbo.Tablename
    WHERE loading_time &lt; '08/27/2014 4:00:00' 

  SET @r = @@ROWCOUNT;

  COMMIT TRANSACTION;
 -- printing if transaction is commited 
  print 'done'
  CHECKPOINT
-- shrinking file after transaction is comminted
  --DBCC SHRINKFILE (2, TRUNCATEONLY); -- CHECKPOINT;    -- if simple
  -- BACKUP LOG ... -- if full
END


Is this the right way to delete data and delete logs, in between user transaction because when I did it I get an error ;


  Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.


Update I have change the code to include checkpoint, but even after adding check point, after 3 hours logs get filled and full rollback takes place.
What should i do to delete ?
Don't shrink the log file - just ask yourself if you'll need to recover to part way through the process, and use this to decide whether to use Full or Simple.

If you're going with Simple, then checkpointing regularly will be enough. Leave the log file large and then consider what to do with it after the whole process. If you shrink it each time it'll only grow for the next iteration.

If you're going with Full, then do a lot of backups, figuring out how to get them off somewhere. Again, leave the log file big and figure out if you need to shrink it at the very end.

Edit based on comments below: making sure the database being checkpointed is the same one as has the growing log file is important too. :)