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  

migrated from stackoverflow.com Jan 12 '12 at 12:58

This question came from our site for professional and enthusiast programmers.

  • Please post the actual query. It sounds like you are in an outer transaction as well. – JNK Jan 12 '12 at 13:05
  • Is TRUNCATE LOG ON CHECKPOINT OPTION on or off? – bernd_k Jan 12 '12 at 15:34
  • 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 '12 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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