Large number of row deletes fails with 'out of log space' I know this is a common question, but I'm at a loss how to fix it.

Table has an indexed date column so I'm using that to delete one day at a time, oldest first. Within the loop that does each day there's another loop that deletes the TOP 1000 rows at a time, the inner, 1000 row, loop is wrapped in BEGIN/COMMIT TRAN block and the outer, daily, loop has a CHECKPOINT statement at the end. As far as I can tell this should mean that each 1000 rows is deleted in a transaction and after each day a CHECKPOINT is issued.

The statement fails with:  The transaction log for database 'database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Checking sys.databases I can see the reason is 'CHECKPOINT' (which is why I added the CHECKPOINT statement to the query). The database is using the SIMPLE recovery model, so as far as I can see this shouldn't be happening. It's also rolling back everything when it fails, because I can see the oldest record's date isn't changing.

I've checked there are no long running transactions locking the log, but I'm wondering if it might be because the db used to be log-shipped (it was moved here from a different server and is no longer). Maybe the transactions are marked for replication, but I don't know how to tell.

Does anyone have any suggestions?

My statement logic looks like this:

@NoOfDays = -10 (for example, with 10 days data to delete)  
while @NoOfDays &lt; 0  
Begin  
    While (1=1)  
    Begin  
        Begin Tran  
        Delete 1000 rows that are older than @NoOfDays  
        End Tran  
        If @@Rowcount &lt; 1000 Break  
    End  
    Checkpoint  
    @NoOfDays += 1  
End
If there is a FULL recovery model - you need to schedule the regular transaction log backups
If not (your case) - try to run your statements in smaller bulks, instead of one huge transaction
and finally the most like the your problem - place your begin tran/commit inside the loop