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  
    While (1=1)  
        Begin Tran  
        Delete 1000 rows that are older than @NoOfDays  
        End Tran  
        If @@Rowcount < 1000 Break  
    @NoOfDays += 1  
  • Please post the actual query. It sounds like you are in an outer transaction as well.
    – JNK
    Jan 12, 2012 at 13:05
    – bernd_k
    Jan 12, 2012 at 15:34

1 Answer 1

  • 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
  • You're right - I'd added the tran statements to my local copy of the statement, not the one that I was running <facepalm>
    – Steve
    Jan 12, 2012 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.