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 < 0
Begin
While (1=1)
Begin
Begin Tran
Delete 1000 rows that are older than @NoOfDays
End Tran
If @@Rowcount < 1000 Break
End
Checkpoint
@NoOfDays += 1
End
